50 Important Queries in SQL Server
Query 1: How to Retrieve List of All Databases in SQL Server The following SQL query retrieves a list of all databases on a SQL Server. EXEC sp_helpdb SQL Example Query 2: How to Display Text of a Stored Procedure, Trigger, or View in SQL Server The following SQL query gets the display text of a stored procedure, trigger, or view on SQL Server. exec sp_helptext @objname = 'Object_Name' SQL Example Query 3: How to List All Stored Procedures in SQL Server The following SQL query gets a list of all stored procedures in a SQL Server database. SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE o.xtype='P' SQL Example To retrieve the View use “V” instead of “P” and for functions use “FN. Query 4: How to Get All Stored Procedures Related To a Table in SQL Server The following SQL query gets all stored procedures related to a database table in SQL Server. SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '%Table_Name%' AND o.xtype='P' SQL Example To retrieve the View use “V” instead of “P” and for functions use “FN. Query 5: How to Rebuild All Indexes of a SQL Server Database The following SQL query rebuilds all indexes of a SQL Server database. EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO EXEC sp_updatestats GO SQL Example Query 6: How to Retrieve All dependencies of Stored Procedures in SQL Server This query returns all object names that are being using in stored procedurs like tables, user define functions, another stored procedures and so on. ;WITH stored_procedures AS ( SELECT oo.name AS table_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_NAme%' ) SELECT Table_name FROM stored_procedures WHERE row = 1 SQL Example Query 7: How to Find Size of a Database Table in SQL Server The following SQL query gets the size of a database table of a SQL Server database. SELECT sob.name AS Table_Name, SUM(sys.length) AS [Size_Table(Bytes)] FROM sysobjects sob, syscolumns sys WHERE sob.xtype='u' AND sys.id=sob.id GROUP BY sob.name SQL Example Query 8: How to get all tables that don’t have identity columns in SQL Server The following SQL query gets all tables of a SQL Server database that don't have identity columns. SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where Table_NAME NOT IN ( SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN sys.identity_columns ic on (c.COLUMN_NAME=ic.NAME)) AND TABLE_TYPE ='BASE TABLE' SQL Example Query 9: How to List Primary Keys and Foreign Keys in a SQL Server Database The following SQL query gets a list of all primary keys and foreign keys on a SQL Server database. SELECT DISTINCT Constraint_Name AS [Constraint], Table_Schema AS [Schema], Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE GO SQL Example Query 10: List of Primary Key and Foreign Key for a particular table SELECT DISTINCT Constraint_Name AS [Constraint], Table_Schema AS [Schema], Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='Table_Name' GO SQL Example Query 11: RESEED Identity of all tables EXEC sp_MSForEachTable ' IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 DBCC CHECKIDENT (''?'', RESEED, 0) SQL Example Query 12: List of tables with number of records CREATE TABLE #Tab ( Table_Name [varchar](max), Total_Records int ); EXEC sp_MSForEachTable @command1=' Insert Into #Tab(Table_Name, Total_Records) SELECT ''?'', COUNT(*) FROM ?' SELECT * FROM #Tab t ORDER BY t.Total_Records DESC; DROP TABLE #Tab; SQL Example Query 13: Get the version name of SQL Server SELECT @@VERSION AS Version_Name SQL Example Query 14: Get Current Language of SQL Server SELECT @@LANGUAGE AS Current_Language; SQL Example Query 15: Disable all constraints of a table ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL SQL Example Query 16: Disable all constraints of all tables EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' SQL Example Query 17: Get Current Language Id SELECT @@LANGID AS 'Language ID' SQL Example Query 18: Get precision level used by decimal and numeric as current set in Server SELECT @@MAX_PRECISION AS 'MAX_PRECISION' SQL Example Query 19: Return Server Name of SQL Server SELECT @@SERVERNAME AS 'Server_Name' SQL Example Query 20: Get name of register key under which SQL Server is running SELECT @@SERVICENAME AS 'Service_Name' SQL Example Query 21: Get Session Id of current user process SELECT @@SPID AS 'Session_Id' SQL Example Query 22: Get Current Value of TEXTSIZE option SELECT @@TEXTSIZE AS 'Text_Size' SQL Example Query 23: Retrieve Free Space of Hard Disk EXEC master..xp_fixeddrives SQL example Query 24: Disable a Particular Trigger Syntax ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name SQL Example ALTER TABLE Employee DISABLE TRIGGER TR_Insert_Salary SQL Query 25: Enable a Particular Trigger Syntax ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name SQL Example ALTER TABLE Employee ENABLE TRIGGER TR_Insert_Salary SQL 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 ALTER TABLE Table_Name DISABLE TRIGGER ALL SQL Example ALTER TABLE Demo DISABLE TRIGGER ALL SQL Query 27: Enable All Trigger of a table ALTER TABLE Table_Name ENABLE TRIGGER ALL SQL Example ALTER TABLE Demo ENABLE TRIGGER ALL SQL Query 28: Disable All Trigger for database Using sp_msforeachtable system stored procedure we enable and disable all triggers for a database. Syntax Use Database_Name Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all" SQL Example example Query 29: Enable All Trigger for database Use Demo Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all" SQL example Query 30: List of Stored procedure modified in last N days SELECT name,modify_date FROM sys.objects WHERE type='P' AND DATEDIFF(D,modify_date,GETDATE())< N SQL example Query 31: List of Stored procedure created in last N days SELECT name,sys.objects.create_date FROM sys.objects WHERE type='P' AND DATEDIFF(D,sys.objects.create_date,GETDATE())< N SQL Example Query 32: Recompile a stored procedure EXEC sp_recompile'Procedure_Name'; GO SQL Example Query 33: Recompile all stored procedure on a table EXEC sp_recompile N'Table_Name'; GO SQL Example Query 34: Get all columns of a specific data type SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id WHERE t.name = 'Data_Type' SQL Example Query 35: Get all Nullable columns of a table SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id WHERE c.is_nullable=0 AND OBJECT_NAME(c.OBJECT_ID)='Table_Name' SQL Example Query 36: Get All table that don’t have primary key SELECT name AS Table_Name FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0 ORDER BY Table_Name; SQL Example Query 37: Get All table that don’t have foreign key SELECT name AS Table_Name FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 0 ORDER BY Table_Name; SQL Example Query 38: Get All table that don’t have identity column SELECT name AS Table_Name FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIdentity') = 0 ORDER BY Table_Name; SQL Example Query 39: Get First Date of Current Month SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105) First_Date_Current_Month; SQL Example Query 40: Get last date of previous month SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())),GETDATE()),105) Last_Date_Previous_Month; SQL Example Query 41: Get last date of current month SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) Last_Date_Current_Month; SQL Example Query 42: Get first date of next month SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())+1),105) First_Date_Next_Month; SQL Example Query 43: Swap the values of two columns UPDATE Table_Name SET Column1=Column2, Column2=Column1 SQL Example Query 44: Remove all stored procedure from database Declare @Drop_SP Nvarchar(MAX) Declare My_Cursor Cursor For Select [name] From sys.objects where type = 'p' Open My_Cursor Fetch Next From My_Cursor Into @Drop_SP While @@FETCH_STATUS= 0 Begin Exec('DROP PROCEDURE ' + @Drop_SP) Fetch Next From My_Cursor Into @Drop_SP End Close My_Cursor Deallocate My_Cursor SQL Example Query 45: Remove all views from database Declare @Drop_View Nvarchar(MAX) Declare My_Cursor Cursor For Select [name] From sys.objects where type = 'v' Open My_Cursor Fetch Next From My_Cursor Into @Drop_View While @@FETCH_STATUS = 0 Begin Exec('DROP VIEW ' + @Drop_View) Fetch Next From My_Cursor Into @Drop_View End Close My_Cursor Deallocate My_Cursor SQL Example Query 46: Drop all tables EXEC sys.sp_MSforeachtable @command1 = 'Drop Table ?' SQL Example Query 47: Get information of tables’ columns SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=’Table_Name’ SQL Example Query 48: Get all columns contain any constraints SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE SQL Example Query 49: Get all tables that contain a view SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE SQL Example Query 50: Get all columns of table that using in views SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE SQL Example
https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/
Query 1: How to Retrieve List of All Databases in SQL Server
The following SQL query retrieves a list of all databases on a SQL Server.
Query 2: How to Display Text of a Stored Procedure, Trigger, or View in SQL Server
The following SQL query gets the display text of a stored procedure, trigger, or view on SQL Server.
Query 3: How to List All Stored Procedures in SQL Server
The following SQL query gets a list of all stored procedures in a SQL Server database.
To retrieve the View use “V” instead of “P” and for functions use “FN.
Query 4: How to Get All Stored Procedures Related To a Table in SQL Server
The following SQL query gets all stored procedures related to a database table in SQL Server.
To retrieve the View use “V” instead of “P” and for functions use “FN.
Query 5: How to Rebuild All Indexes of a SQL Server Database
The following SQL query rebuilds all indexes of a SQL Server database.
Query 6: How to Retrieve All dependencies of Stored Procedures in SQL Server
This query returns all object names that are being using in stored procedurs like tables, user define functions, another stored procedures and so on.
Query 7: How to Find Size of a Database Table in SQL Server
The following SQL query gets the size of a database table of a SQL Server database.
Query 8: How to get all tables that don’t have identity columns in SQL Server
The following SQL query gets all tables of a SQL Server database that don't have identity columns.
Query 9: How to List Primary Keys and Foreign Keys in a SQL Server Database
The following SQL query gets a list of all primary keys and foreign keys on a SQL Server database.
Query 10: List of Primary Key and Foreign Key for a particular table
Query 11: RESEED Identity of all tables
Query 12: List of tables with number of records
Query 13: Get the version name of SQL Server
Query 14: Get Current Language of SQL Server
Query 15: Disable all constraints of a table
Query 16: Disable all constraints of all tables
Query 17: Get Current Language Id
Query 18: Get precision level used by decimal and numeric as current set in Server
Query 19: Return Server Name of SQL Server
Query 20: Get name of register key under which SQL Server is running
Query 21: Get Session Id of current user process
Query 22: Get Current Value of TEXTSIZE option
Query 23: Retrieve Free Space of Hard Disk
Query 24: Disable a Particular Trigger
Syntax
Example
Query 25: Enable a Particular Trigger
Syntax
Example
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
Example
Query 27: Enable All Trigger of a table
Example
Query 28: Disable All Trigger for database
Using sp_msforeachtable system stored procedure we enable and disable all triggers for a database.
Syntax
Example
Query 29: Enable All Trigger for database
Query 30: List of Stored procedure modified in last N days
Query 31: List of Stored procedure created in last N days
Query 32: Recompile a stored procedure
Query 33: Recompile all stored procedure on a table
Query 34: Get all columns of a specific data type
Query 35: Get all Nullable columns of a table
Query 36: Get All table that don’t have primary key
Query 37: Get All table that don’t have foreign key
Query 38: Get All table that don’t have identity column
Query 39: Get First Date of Current Month
Query 40: Get last date of previous month
Query 41: Get last date of current month
Query 42: Get first date of next month
Query 43: Swap the values of two columns
Query 44: Remove all stored procedure from database
Query 45: Remove all views from database
Query 46: Drop all tables
Query 47: Get information of tables’ columns
Query 48: Get all columns contain any constraints
Query 49: Get all tables that contain a view
Query 50: Get all columns of table that using in views
Read more articles on SQL Queries:
![](https://files-cdn.cnblogs.com/files/thingk/%E5%BE%AE%E4%BF%A1%E6%94%B6%E6%AC%BE%E7%A0%81.bmp)