15 Best SQL Queries for Developers [SQL Queries]

15 Best SQL Queries for Developers [SQL Queries]

In this Article, I will explain some important “SQL Server Queries”. I think “most of each SQL Developer” needs these important queries in the daily life!

How to use below listed SQL Quires”? And “What are the Advantages”?

As per this, I am tried to write these important “SQL Queries” and sharing with you! If you have any other smart query related to this (You can share to me and I will publish this post with your query!)


List of Queries with Result As,

--GET LIST OF DATABASES
EXEC SP_HELPDB

-- RESULT
name db_size owner dbid created status compatibility_level
CLR_Demo_BD       7.00 MB sa 7 Dec 30 2016 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110
Demo       7.00 MB sa 8 Jan  2 2017 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110
master       6.63 MB sa 1 Apr  8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 110
model       5.31 MB sa 3 Apr  8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 110
msdb      21.75 MB sa 4 Feb 10 2012 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110
ReportServer      15.19 MB sa 5 Sep 14 2016 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=706, Collation=Latin1_General_CI_AS_KS_WS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110
ReportServerTempDB       5.38 MB Lenovo-08\Viainda 6 May 26 2016 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706, Collation=Latin1_General_CI_AS_KS_WS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110
tempdb       8.75 MB sa 2 Dec 20 2016 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 110

-- DISPLAY TEXT FOR PROCEDURE, TRIGGER, VIEW
EXEC SP_HELPTEXT @objname = 'Object_Name'

--EXAMPLE FOR DISPLAY TEXT
EXEC SP_HELPTEXT @objname = 'dbo.SP_Get_Students'

--RESULT
Text
CREATE PROCEDURE SP_Get_Students
AS
BEGIN
 SET NOCOUNT ON;

    -- SELECT STATEMENTS FOR PROCEDURE HERE

 SELECT * FROM [dbo].[Students]
END

--GET SQL SERVER VERSION NAME
SELECT @@VERSION AS VersionName  

--RESULT
VersionName
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
 Feb 10 2012 19:39:15 
 Copyright (c) Microsoft Corporation
 Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

--GET CURRENT SQL SERVER LANGUAGE 
SELECT @@LANGUAGE AS CurrentUsedLanguage

-- RESULT
CurrentUsedLanguage
us_english

--GET CURRENT LANGUAGE ID
SELECT @@LANGID AS 'LanguageID'  

--EXAMPLE
SET LANGUAGE 'English' 
SELECT @@LANGID AS 'LanguageID'  

-- RESULT
LanguageID
0

--GET LIST OF DATABASE PROCEDURES
SELECT DISTINCT SO.NAME, SO.XTYPE  
 FROM SYSCOMMENTS SC    
  INNER JOIN SYSOBJECTS SO ON SC.ID=SO.ID  
WHERE SO.XTYPE ='P'

--OR

-- USING LIKE FOR SYSCOMMENTS
SELECT DISTINCT SO.NAME, SO.XTYPE  
 FROM SYSCOMMENTS SC
  INNER JOIN SYSOBJECTS SO ON SC.ID=SO.ID  
WHERE SC.TEXT LIKE '%SP_%'

-- RESULT
NAME              XTYPE
SP_Email_Approvals     P 
SP_Get_Students        P 

-- REBUILD ALL DATABASE INDEX
GO
 EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"  
GO  
 EXEC sp_updatestats  
GO 

-- RESULT
[dbo].[Students]
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Updating [dbo].[Students]
    [PK_Students], update is not necessary...
    0 index(es)/statistic(s) have been updated, 1 did not require update.
 
Updating [sys].[queue_messages_1977058079]
    [queue_clustered_index], update is not necessary...
    [queue_secondary_index], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[queue_messages_2009058193]
    [queue_clustered_index], update is not necessary...
    [queue_secondary_index], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[queue_messages_2041058307]
    [queue_clustered_index], update is not necessary...
    [queue_secondary_index], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[filestream_tombstone_2073058421]
    [FSTSClusIdx], update is not necessary...
    [FSTSNCIdx], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[syscommittab]
    [ci_commit_ts], update is not necessary...
    [si_xdes_id], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[filetable_updates_2105058535]
    [FFtUpdateIdx], update is not necessary...
    0 index(es)/statistic(s) have been updated, 1 did not require update.
 
Statistics for all tables have been updated.

--GET ALL TABLE NAME USED IN PROCEDURES
WITH GET_Procedures AS (  
  SELECT oo.name AS GET_UsedTable_name,  
  ROW_NUMBER() OVER(partition by o.name, oo.name ORDER BY o.name, oo.name) AS ROW  
  FROM SYSDEPENDS d    
  INNER JOIN SYSOBJECTS o ON o.id=d.id  
  INNER JOIN SYSOBJECTS oo ON oo.id=d.depid  
  WHERE o.xtype = 'P' AND o.name LIKE '%SP_%' 
)
SELECT GET_UsedTable_name FROM GET_Procedures 
WHERE ROW = 1

--RESULT
GET_UsedTable_name
Students

-- List of Table Primary and Foreign Keys
 SELECT DISTINCT Constraint_Name AS [ConstraintName],
  Table_Schema AS [SchemaName], 
  Table_Name AS [TableName] 
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
 WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='Students'
GO
-- RESULT
ConstraintName SchemaName TableName
PK_Students dbo         Students

--DISABLE ALL CONSTRAINTS OF A TABLE
ALTER TABLE Students NOCHECK CONSTRAINT ALL

--RESULT
Command(s) completed successfully.

--DISABLE ALL CONSTRAINTS OF ALL TABLES
EXEC SP_MSFOREACHTABLE 'ALTER TABLE? NOCHECK CONSTRAINT ALL' 

--RESULT
Command(s) completed successfully.

-- CHANGE DEFAULT LANGUAGE FOR SQL SERVER
GO
 EXEC SP_CONFIGURE 'DEFAULT LANGUAGE', 23;
GO
 RECONFIGURE WITH OVERRIDE;
GO

--RESULT
Configuration option 'default language' changed from 0 to 23. 
Run the RECONFIGURE statement to install.


I hope you are enjoying with this post! Please share with you friends. Thank you!!
ANIL SINGH

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Google Amazon and Flipkart Book Store!

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.