General purpose queries:

Query 1: Retrieve List of All Database

 
  1. EXEC sp_helpdb  
Example
 

Query 2: Display Text of Stored Procedure, Trigger, View

 
  1. exec sp_helptext @objname = 'Object_Name'  
Example
 

Query 3: Get All Stored Procedure Relate To Database

 
  1. SELECT DISTINCT o.name, o.xtype  
  2.   
  3. FROM syscomments c  
  4.   
  5. INNER JOIN sysobjects o ON c.id=o.id  
  6.   
  7. WHERE o.xtype='P'  
Example
 
To retrieve the View use “V” instead of “P” and for functions use “FN.
 

Query 4: Get All Stored Procedure Relate To Table

 
  1. SELECT DISTINCT o.name, o.xtype  
  2.   
  3. FROM syscomments c  
  4.   
  5. INNER JOIN sysobjects o ON c.id=o.id  
  6.   
  7. WHERE c.TEXT LIKE '%Table_Name%' AND o.xtype='P'  
Example
 
To retrieve the View use “V” instead of “P” and for functions use “FN.
 

Query 5: Rebuild All Index of Database

 
  1. EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"  
  2.   
  3. GO  
  4.   
  5. EXEC sp_updatestats  
  6.   
  7. GO
Example
 

Query 6: Retrieve All dependencies of Stored Procedure

 
This query return all objects name that are using into stored procedure like table, user define function, another stored procedure.
  1. ;WITH stored_procedures AS (  
  2.   
  3. SELECT  
  4.   
  5. oo.name AS table_name,  
  6.   
  7. ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.nameAS row  
  8.   
  9. FROM sysdepends d  
  10.   
  11. INNER JOIN sysobjects o ON o.id=d.id  
  12.   
  13. INNER JOIN sysobjects oo ON oo.id=d.depid  
  14.   
  15. WHERE o.xtype = 'P' AND o.name LIKE '%SP_NAme%' )  
  16.   
  17. SELECT Table_name FROM stored_procedures  
  18.   
  19. WHERE row = 1  
Example
 

Query 7: Find Byte Size Of All tables in database

 
  1. SELECT sob.name AS Table_Name,  
  2.   
  3. SUM(sys.length) AS [Size_Table(Bytes)]  
  4.   
  5. FROM sysobjects sob, syscolumns sys  
  6.   
  7. WHERE sob.xtype='u' AND sys.id=sob.id  
  8.   
  9. GROUP BY sob.name
Example
 

Query 8: Get all table that don’t have identity column

 
  1. SELECT  
  2.   
  3. TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
  4.   
  5. where  
  6.   
  7. Table_NAME NOT IN  
  8.   
  9. (  
  10.   
  11. SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c  
  12.   
  13. INNER  
  14.   
  15. JOIN sys.identity_columns ic  
  16.   
  17. on  
  18.   
  19. (c.COLUMN_NAME=ic.NAME))  
  20.   
  21. AND  
  22.   
  23. TABLE_TYPE ='BASE TABLE'  
Example
 

Query 9: List of Primary Key and Foreign Key for Whole Database

 
  1. SELECT  
  2.   
  3. DISTINCT  
  4.   
  5. Constraint_Name AS [Constraint],  
  6.   
  7. Table_Schema AS [Schema],  
  8.   
  9. Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
  10.   
  11. GO
Example
 

Query 10: List of Primary Key and Foreign Key for a particular table

 
  1. SELECT  
  2.   
  3. DISTINCT  
  4.   
  5. Constraint_Name AS [Constraint],  
  6.   
  7. Table_Schema AS [Schema],  
  8.   
  9. Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
  10.   
  11. WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='Table_Name'  
  12.   
  13. GO
Example
 

Query 11: RESEED Identity of all tables

 
  1. EXEC sp_MSForEachTable '  
  2.   
  3. IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1  
  4.   
  5. DBCC CHECKIDENT (''?'', RESEED, 0) 
Example
 

Query 12: List of tables with number of records

 
  1. CREATE TABLE #Tab  
  2.   
  3. (  
  4.   
  5. Table_Name [varchar](max),  
  6.   
  7. Total_Records int  
  8.   
  9. );  
  10.   
  11. EXEC sp_MSForEachTable @command1=' Insert Into #Tab(Table_Name, Total_Records) SELECT ''?'', COUNT(*) FROM ?'  
  12.   
  13. SELECT * FROM #Tab t ORDER BY t.Total_Records DESC;  
  14.   
  15. DROP TABLE #Tab;  
Example
 

Query 13: Get the version name of SQL Server

 
  1. SELECT @@VERSION AS Version_Name  
Example
 

Query 14: Get Current Language of SQL Server

 
  1. SELECT @@LANGUAGE AS Current_Language;  
Example
 

Query 15: Disable all constraints of a table

 
  1. ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL  
Example
 

Query 16: Disable all constraints of all tables

 
  1. EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
Example
 

Query 17: Get Current Language Id

 
  1. SELECT @@LANGID AS 'Language ID'  
Example
 

Query 18: Get precision level used by decimal and numeric as current set in Server

 
  1. SELECT @@MAX_PRECISION AS 'MAX_PRECISION'  
Example
 

Query 19: Return Server Name of SQL Server

 
  1. SELECT @@SERVERNAME AS 'Server_Name'  
Example
 

Query 20: Get name of register key under which SQL Server is running

 
  1. SELECT @@SERVICENAME AS 'Service_Name'
Example
 

Query 21: Get Session Id of current user process

 
  1. SELECT @@SPID AS 'Session_Id'
Example
 

Query 22: Get Current Value of TEXTSIZE option

 
  1. SELECT @@TEXTSIZE AS 'Text_Size'  
Example
 

Query 23: Retrieve Free Space of Hard Disk

 
  1. EXEC master..xp_fixeddrives  
example
 

Query 24: Disable a Particular Trigger


Syntax
  1. ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name  
Example
 
  1. ALTER TABLE Employee DISABLE TRIGGER TR_Insert_Salary  

Query 25: Enable a Particular Trigger

 
Syntax
  1. ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name  
Example
 
  1. ALTER TABLE Employee ENABLE TRIGGER TR_Insert_Salary  

Query 26: Disable All Trigger of a table

 
We can disable and enable all triggers of a table using previous query, but replacing the "ALL" instead of trigger name.
Syntax
  1. ALTER TABLE Table_Name DISABLE TRIGGER ALL  
Example
 
  1. ALTER TABLE Demo DISABLE TRIGGER ALL  

Query 27: Enable All Trigger of a table

 
  1. ALTER TABLE Table_Name ENABLE TRIGGER ALL  
Example
 
  1. ALTER TABLE Demo ENABLE TRIGGER ALL  

Query 28: Disable All Trigger for database

 
Using sp_msforeachtable system stored procedure we enable and disable all triggers for a database.
 
Syntax
 
  1. Use Database_Name  
  2.   
  3. Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"  
Example
example
 

Query 29: Enable All Trigger for database

 
  1. Use Demo  
  2.   
  3. Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"  
example
 

Query 30: List of Stored procedure modified in last N days

 
  1. SELECT name,modify_date  
  2.   
  3. FROM sys.objects  
  4.   
  5. WHERE type='P'  
  6.   
  7. AND DATEDIFF(D,modify_date,GETDATE())< N  
 
example
 

Query 31: List of Stored procedure created in last N days

 
  1. SELECT name,sys.objects.create_date  
  2.   
  3. FROM sys.objects  
  4.   
  5. WHERE type='P'  
  6.   
  7. AND DATEDIFF(D,sys.objects.create_date,GETDATE())< N  
 
Example
 

Query 32: Recompile a stored procedure

 
  1. EXEC sp_recompile'Procedure_Name';  
  2.   
  3. GO  
Example
 

Query 33: Recompile all stored procedure on a table

 
  1. EXEC sp_recompile N'Table_Name';  
  2.   
  3. GO  
Example
 

Query 34: Get all columns of a specific data type

 
  1. SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name  
  2.   
  3. FROM sys.columns AS c  
  4.   
  5. JOIN sys.types AS t ON c.user_type_id=t.user_type_id  
  6.   
  7. WHERE t.name = 'Data_Type'  
 
Example
 

Query 35: Get all Nullable columns of a table

 
  1. SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name  
  2.   
  3. FROM sys.columns AS c  
  4.   
  5. JOIN sys.types AS t ON c.user_type_id=t.user_type_id  
  6.   
  7. WHERE c.is_nullable=0 AND OBJECT_NAME(c.OBJECT_ID)='Table_Name'  
 
Example
 

Query 36: Get All table that don’t have primary key

 
  1. SELECT name AS Table_Name  
  2.   
  3. FROM sys.tables  
  4.   
  5. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0  
  6.   
  7. ORDER BY Table_Name;  
 
Example
 

Query 37: Get All table that don’t have foreign key

 
  1. SELECT name AS Table_Name  
  2.   
  3. FROM sys.tables  
  4.   
  5. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 0  
  6.   
  7. ORDER BY Table_Name;  
Example
 

Query 38: Get All table that don’t have identity column

 
  1. SELECT name AS Table_Name  
  2.   
  3. FROM sys.tables  
  4.   
  5. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIdentity') = 0  
  6.   
  7. ORDER BY Table_Name;  
 
Example
 

Query 39: Get First Date of Current Month

 
  1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105) First_Date_Current_Month;  
 
Example
 

Query 40: Get last date of previous month

 
  1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())),GETDATE()),105) Last_Date_Previous_Month;  
 
Example
 

Query 41: Get last date of current month

 
  1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) Last_Date_Current_Month;  
 
Example
 

Query 42: Get first date of next month

  1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())+1),105) First_Date_Next_Month;  
 
Example
 

Query 43: Swap the values of two columns

 
  1. UPDATE Table_Name SET Column1=Column2, Column2=Column1  
 
Example
 

Query 44: Remove all stored procedure from database

 
  1. Declare @Drop_SP Nvarchar(MAX)  
  2.   
  3. Declare My_Cursor Cursor For Select [nameFrom sys.objects where type = 'p'  
  4.   
  5. Open My_Cursor  
  6.   
  7. Fetch Next From My_Cursor Into @Drop_SP  
  8.   
  9. While @@FETCH_STATUS= 0  
  10.   
  11. Begin  
  12.   
  13. Exec('DROP PROCEDURE ' + @Drop_SP)  
  14.   
  15. Fetch Next From My_Cursor Into @Drop_SP  
  16.   
  17. End  
  18.   
  19. Close My_Cursor  
  20.   
  21. Deallocate My_Cursor  
 
Example
 

Query 45: Remove all views from database

 
  1. Declare @Drop_View Nvarchar(MAX)  
  2.   
  3. Declare My_Cursor Cursor For Select [nameFrom sys.objects where type = 'v'  
  4.   
  5. Open My_Cursor  
  6.   
  7. Fetch Next From My_Cursor Into @Drop_View  
  8.   
  9. While @@FETCH_STATUS = 0  
  10.   
  11. Begin  
  12.   
  13. Exec('DROP VIEW ' + @Drop_View)  
  14.   
  15. Fetch Next From My_Cursor Into @Drop_View  
  16.   
  17. End  
  18.   
  19. Close My_Cursor  
  20.   
  21. Deallocate My_Cursor  
 
Example
 

Query 46: Drop all tables

 
  1. EXEC sys.sp_MSforeachtable @command1 = 'Drop Table ?'  
 
Example
 

Query 47: Get information of tables’ columns

 
  1. SELECT * FROM INFORMATION_SCHEMA.COLUMNS  
  2.   
  3. WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=’Table_Name’  
 
Example
 

Query 48: Get all columns contain any constraints

 
  1. SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE  
 
Example
 

Query 49: Get all tables that contain a view

 
  1. SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE  
 
Example
 

Query 50: Get all columns of table that using in views

 
  1. SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE  
 
Example
 

Comments

Popular posts from this blog