Post
Ask Question

Some Important Collection of SQL Server Query

Introduction

This code snippet contains list of sql query that perform very useful task. Every SQL server developer should be aware of these query.

  1. --Count Number Of Stored Procedure and Table in DB  
  2. SELECT  
  3. CASE TYPE  
  4. WHEN 'U'  
  5. THEN 'User Defined Tables'  
  6. WHEN 'S'  
  7. THEN 'System Tables'  
  8. WHEN 'IT'  
  9. THEN 'Internal Tables'  
  10. WHEN 'P'  
  11. THEN 'Stored Procedures'  
  12. WHEN 'PC'  
  13. THEN 'CLR Stored Procedures'  
  14. WHEN 'X'  
  15. THEN 'Extended Stored Procedures'  
  16. END,  
  17. COUNT(*)  
  18. FROM SYS.OBJECTS  
  19. WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')  
  20. GROUP BY TYPE  
  21. select Count(*) from sys.procedures  
  22. --If you want to find it within a period then;  
  23. SELECT name  
  24. FROM sys.objects  
  25. WHERE type = 'P'  
  26. AND DATEDIFF(D,create_date, GETDATE()) <100  
  27. --Count Number Of Constrain in Table in DB  
  28. SELECT 'Count' = COUNT(*), 'Type' = CASE type  
  29. WHEN 'C' THEN 'CHECK constraints'  
  30. WHEN 'D' THEN 'Default or DEFAULT constraints'  
  31. WHEN 'F' THEN 'FOREIGN KEY constraints'  
  32. WHEN 'FN' THEN 'Scalar functions'  
  33. WHEN 'IF' THEN 'Inlined table-functions'  
  34. WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraints'  
  35. WHEN 'L' THEN 'Logs'  
  36. WHEN 'P' THEN 'Stored procedures'  
  37. WHEN 'R' THEN 'Rules'  
  38. WHEN 'RF' THEN 'Replication filter stored procedures'  
  39. WHEN 'S' THEN 'System tables'  
  40. WHEN 'TF' THEN 'Table functions'  
  41. WHEN 'TR' THEN 'Triggers'  
  42. WHEN 'U' THEN 'User tables'  
  43. WHEN 'V' THEN 'Views'  
  44. WHEN 'X' THEN 'Extended stored procedures'  
  45. END  
  46. FROM sys.objects  
  47. GROUP BY type  
  48. ORDER BY type  
  49. GO  
  50. --How to drop all stored procedures at once in SQL Server database?  
  51. declare @procName varchar(500)  
  52. declare cur cursor  
  53. for select [name] from sys.objects where type = 'p'  
  54. open cur  
  55. fetch next from cur into @procName  
  56. while @@fetch_status = 0  
  57. begin  
  58. exec('drop procedure ' + @procName)  
  59. fetch next from cur into @procName  
  60. end  
  61. close cur  
  62. deallocate cur  
  63. DECLARE @sql NVARCHAR(MAX) = N'';  
  64. SELECT @sql += N'DROP PROCEDURE dbo.'  
  65. + QUOTENAME(name) + ';  
  66. ' FROM sys.procedures  
  67. WHERE name LIKE N'sp[_]%'  
  68. AND SCHEMA_NAME(schema_id) = N'dbo';  
  69. EXEC sp_executesql @sql;  
  70. --Remove all Tables  
  71. -- drop all user defined tables  
  72. EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"  
  73. --Remove all User-defined Stored Procedures  
  74. -- drop all user defined stored procedures  
  75. Declare @procName varchar(500)  
  76. Declare cur Cursor For Select [name] From sys.objects where type = 'p'  
  77. Open cur  
  78. Fetch Next From cur Into @procName  
  79. While @@fetch_status = 0  
  80. Begin  
  81. Exec('drop procedure ' + @procName)  
  82. Fetch Next From cur Into @procName  
  83. End  
  84. Close cur  
  85. Deallocate cur  
  86. --Remove all Views  
  87. -- drop all user defined views  
  88. Declare @viewName varchar(500)  
  89. Declare cur Cursor For Select [name] From sys.objects where type = 'v'  
  90. Open cur  
  91. Fetch Next From cur Into @viewName  
  92. While @@fetch_status = 0  
  93. Begin  
  94. Exec('drop view ' + @viewName)  
  95. Fetch Next From cur Into @viewName  
  96. End  
  97. Close cur  
  98. Deallocate cur  
  99. --Remove all Triggers  
  100. -- drop all user defined triggers  
  101. Declare @trgName varchar(500)  
  102. Declare cur Cursor For Select [name] From sys.objects where type = 'tr'  
  103. Open cur  
  104. Fetch Next From cur Into @trgName  
  105. While @@fetch_status = 0  
  106. Begin  
  107. Exec('drop trigger ' + @trgName)  
  108. Fetch Next From cur Into @trgName  
  109. End  
  110. Close cur  
  111. Deallocate cur  
  112. --Get the related Stored Procedure to a table  
  113. SELECT DISTINCT o.name, o.xtype  
  114. FROM syscomments c  
  115. INNER JOIN sysobjects o ON c.id=o.id  
  116. WHERE c.TEXT LIKE '%Yourtablename%'  
  117. --Or you can use following query  
  118. SELECT Name  
  119. FROM sys.procedures  
  120. WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%yourtablename%'  
  121. --Get the related table to a Stored Procedure  
  122. ;WITH stored_procedures AS (  
  123. SELECT  
  124. o.name AS proc_name, oo.name AS table_name,  
  125. ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row  
  126. FROM sysdepends d  
  127. INNER JOIN sysobjects o ON o.id=d.id  
  128. INNER JOIN sysobjects oo ON oo.id=d.depid  
  129. WHERE o.xtype = 'P')  
  130. SELECT proc_name, table_name FROM stored_procedures  
  131. WHERE row = 1  
  132. AND proc_name LIKE '%YourStoredProcedureName%'  
  133. ORDER BY proc_name,table_name  
  134. --Get the related trigger to a table  
  135. select so.name, text  
  136. from sysobjects so, syscomments sc  
  137. where type = 'TR'  
  138. and so.id = sc.id and text like '%YourTableName%'  
  139. --Get the related views to a table  
  140. SELECT view_name, Table_Name  
  141. FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE  
  142. WHERE Table_Name= 'YourTableName'  
  143. ORDER BY view_name, table_name  
  144. --Get the related table to a view  
  145. SELECT view_name, Table_Name  
  146. FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE  
  147. WHERE View_Name = 'YourViewName'  
  148. ORDER BY view_name, table_name  
  149. --Get the tables that do not have an identity column  
  150. SELECT  
  151. TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
  152. where  
  153. Table_NAME NOT IN  
  154. (  
  155. SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c  
  156. INNER  
  157. JOIN sys.identity_columns ic  
  158. on  
  159. (c.COLUMN_NAME=ic.NAME))  
  160. AND  
  161. TABLE_TYPE ='BASE TABLE'  
  162. --Get a list of tables with the number of records of each table  
  163. CREATE TABLE #counts  
  164. (  
  165. table_name varchar(255),  
  166. row_count int  
  167. )  
  168. EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC  
  169. --List all the databases on SQL Servers.  
  170. EXEC sp_databases  
  171. EXEC sp_helpdb  
  172. SELECT name  
  173. FROM sys.databases  
  174. SELECT name  
  175. FROM sys.sysdatabases  
  176. --List all User Defined Functions in the database on SQL Servers.  
  177. SELECT *  
  178. FROM sys.objects  
  179. WHERE type_desc LIKE '%FUNCTION%';  
  180. --Date Time Related Query In sql Server  
  181. DECLARE @DateCounter DATETIME  
  182. SET @DateCounter = GETDATE();  
  183. SELECT  
  184. YEAR(@DateCounter) * 10000+MONTH(@DateCounter)* 100+DAY(@DateCounter) AS [DateId]  
  185. ,CONVERT(char(10), @DateCounter,126) AS [Date]  
  186. ,DATENAME(WEEKDAY,@DateCounter) AS [Day]  
  187. ,DATEPART("WW",@DateCounter) AS WeekOfYear  
  188. ,DATENAME(MONTH,@DateCounter) AS [MonthName]  
  189. ,MONTH(@DateCounter) AS DateMonthNumber  
  190. ,'Q'+CAST(DATEPART(QQ,@DateCounter) AS VARCHAR) AS QuarterCode  
  191. ,YEAR(@DateCounter) AS DateYear  
  192. ,DATENAME(WEEKDAY,@DateCounter)+CAST(DATEPART(DD,@DateCounter) AS VARCHAR)+'Q'+CAST(DATEPART(QQ,@DateCounter) AS VARCHAR)+DATENAME(MONTH,@DateCounter)+DATENAME(YEAR,@DateCounter) AS DateDescription  
  193. ,SUBSTRING(CONVERT(CHAR(10), @DateCounter,126),1,7) AS YearMonthNumber  
  194. ,DATENAME(YEAR,@DateCounter)+ '-' + SUBSTRING(DATENAME(MONTH,@DateCounter),1,3) AS YearMonthCode  
  195. ,DATENAME(YEAR,@DateCounter)+ '-' + 'Q' +CAST(DATEPART(QQ,@DateCounter) AS VARCHAR) AS YearQuarterCode  
  196. ,CONVERT(VARCHAR, @DateCounter,103) AS FormDate  
  197. ,CASE WHEN DATEPART("dw",@DateCounter)=1 THEN 'Sun'  
  198. WHEN DATEPART("dw",@DateCounter)=2 THEN 'Mon'  
  199. WHEN DATEPART("dw",@DateCounter)=3 THEN 'Tues'  
  200. WHEN DATEPART("dw",@DateCounter)=4 THEN 'Weds'  
  201. WHEN DATEPART("dw",@DateCounter)=5 THEN 'Thus'  
  202. WHEN DATEPART("dw",@DateCounter)=6 THEN 'Fri'  
  203. WHEN DATEPART("dw",@DateCounter)=7 THEN 'Sat'  
  204. END AS DayCode  
  205. ,CASE WHEN CONVERT(VARCHAR(10), @DateCounter, 110) = CONVERT(VARCHAR(10), GETDATE(), 110) THEN 'Y' ELSE 'N' END AS IsCurrentDay  
  206. ,CASE WHEN CONVERT(VARCHAR(10), @DateCounter, 110) = CONVERT(VARCHAR(10), GETDATE()-1, 110) THEN 'Y' ELSE 'N' END AS IsPreviousDay  
  207. ,CASE WHEN DATENAME(WEEKDAY,@DateCounter) NOT IN ('Sunday','Saturday') THEN 'Y' ELSE 'N' END AS IsWeekday  
  208. ,CASE WHEN DATENAME(WEEKDAY,@DateCounter) IN ('Sunday','Saturday') THEN 'Y' ELSE 'N' END AS IsWeekendDay  
  209. ,DATEPART("dw",@DateCounter) AS DayOfWeek  
  210. ,CASE WHEN DATEPART("dw",@DateCounter)=2 THEN 'Y' ELSE 'N' END AS IsFirstDayOfWeek  
  211. ,CASE WHEN DATEPART("dw",@DateCounter)=7 THEN 'Y' ELSE 'N' END AS IsLastDayOfWeek  
  212. ,DAY(@DateCounter) AS [DayOfMonth]  
  213. ,CASE WHEN CONVERT(VARCHAR(10), @DateCounter, 110) = CONVERT(VARCHAR(10),DATEADD(dd, -(DATEPART(dd, GETDATE()) + 1), GETDATE()),110.. 

Summary

In this illustration we came to learn about a lot of very important query. Please put your valuable comments.