Want create site? Find Free WordPress Themes and plugins.

This article lists out the fifty frequently used queries by the Sql Server Developers. The next article (i.e part 2 of this article) lists out another 50 frequently used queries by the Sql Server Developers. Hope you will like this article and if any of your commonly used query is not listed here, please let me know. I will definitely post it and we can help together to the Sql Server Developers community

1. How to check if a Database exists in Sql Server

We can use DB_ID() function like below to check if a database exists. Here in the below script we are checking the existence of the database SqlHintsDB. We can as well use the sys.databases Catalog View to check the existence of the database.


2. How to check if a Table exists in Sql Server

We can use the sys.Tables catalog view to check the existence of the Table. Here in the below script we are checking the existence of the table Customers


3. How to check if a Temp table exists in Sql Server

Below script shows how we can check the existence of a Temporary Table. As we know temp tables are created in TempDB database, so we need to check the existence of the temp table in TempDB database. In the below script we are checking the existence of the temp table #TempTable


 
4. How to check if a Stored Procedure exists in Sql Server

We can use the sys.procedures catalog view to check the existence of a stored proedure. Here in the below script we are checking the existence of the stored procedure GetCustomers


 

5. How to check if a Function exists in Sql Server

We can use the sys.objects catalog view to check the existence of a User Defined Function. Here in the below script we are checking the existence of the User Defined Function GetEmployeeDetail

 

6. How to check if a VIEW exists in Sql Server

We can use the sys.views catalog view to check the existence of a View. Here in the below script we are checking the existence of the View vwGetCustomerInfo


 

7. How to check if an Index exists in Sql Server

We can use sys.indexes catalog view to check the existence of a Clustered and Non-Clustered indexes. We can execute a query like below to check the existence of a Index IX_Customer_Id on the Customer table created with a default schema (i.e. dbo).


 

8. How to find all the tables with no indexes in Sql Server

We can write a query like below to get all the Tables in the Database that don’t have any indexes:


 

9. How to find all the indexes that have included columns

We can write a query like below to get the name of all the indexes that have included columns in it and the name of the table to which the index belongs to:


 

10. How to find all the filtered indexes or all the tables having filtered indexes in Sql Server

We can write a query like below to get the name of all the filtered indexes or all the tables having filtered indexes in Sql Server:


 

11. How to get all HEAP Tables or Tables without Clustered Index in Sql Server

A Table that doesn’t have a Clustered Index is referred to as a HEAP Table. We can write a query like below to get all the HEAP Tables or tables that doesn’t have Clustered Index:


 

12. How to get all the Tables with Primary Key Constraint in Sql Server

We can write a query like below to get all the Tables with Primary key constraint:


 

13. How to get all the Tables without Primary Key Constraint in Sql Server

We can write a query like below to get all the Tables without Primary key constraint:


 

14. How to get all the Tables with Non-Clustered Indexes in Sql Server

We can write a query like below to get all the Tables with Non-Clustered indexes:

 

 

15. How to get all the Tables without any Non-Clustered Indexes in Sql Server

We can write a query like below to get all the Tables without any Non-Clustered indexes:


 

16. How to get all the Tables with an Identity column in Sql Server

We can write a query like below to get all the Tables with Identity column:


 

17. How to get all the Tables without an Identity column in Sql Server

We can write a query like below to get all the Tables without Identity column:

 

18. How to find all the Stored Procedures having a given text in it

We can write a script like below to get all the stored all the Stored Procedures having a given text in its definition. Here we are searching for the text SearchString in all the stored procedures


 

19. How to find all tables that have specified column name in Sql Server

We can use a script like below to find all the tables in the database that have column with specified name in it. Here we are searching for all the tables that have columns with a name having a text ColumnName in it.


 

20. How to find all dependencies of a table in Sql Server

We can use the Dynamic Management Function sys.dm_sql_referencing_entities to get all the entities in the current database that refer to the specified table. In this script we are trying to get the Employee tables dependencies

 

21. How to find referenced/dependent objects (like Table, Function etc) of a Stored Procedure/Function in Sql Server

We can use the Dynamic Management Function sys.dm_sql_referenced_entities to get all the entities in the current database which are referenced by a stored procedure or function. Now we can use a script like below to find all the entities in the current database which are referenced by the stored procedure dbo.GetEmployeeDetails


 

22. How to get first day of the previous quarter


 

23. How to get first day of the current quarter


 

24. How to get first day of the next quarter


 

25. How to get first day of the quarter for any given date


 

26. How to get last day of the previous quarter

 

27. How to get last day of the current quarter


 

28. How to get last day of the next quarter


 

29. How to get last day of the quarter for any given date


 
30. How to get first day of the previous month

 

31. How to get first day of the current month

 

32. How to get first day of the next month


 

33. How to get first day of the month for any given date

 

34. How to get last day of the previous month

 

35. How to get last day of the current month


 

36. How to get last day of the next month

 

37. How to get last day of the month for any given date


 

38. How to get all tables in a Database

We can use the sys.Tables catalog view to get the list of all the tables in a database. Here in the below script we are trying to get list of all the tables in the SqlhintsDemoDB database


 

39. How to get all stored procedures in a database

We can use sys.procedures catalog view to get the list of all the stored procedures in a database


 

40. How to get all functions in a database

We can use sys.objects catalog view as shown in the below script to get all the functions in a database. Here joining with sys.sql_modules to display the function definition.

 

41. How to check the definition or content of a stored procedure in Sql Server

We can use the system stored procedure sp_helptext to check the definition of a Stored Proccedure in Sql Server. In the below example we are using the sp_helptext system stored procedure to check the definition of the Stored Procedure GetCityCustomers.


 

42. How to check if a record exists in a table in Sql Server

Below example script checks the existence of the customer record with CustId = 2 in the IF statement


 

43. How to rename column name in Sql Server

We can use the system stored procedure SP_RENAME to rename the table column. Below is the SYNTAX of the SP_RENAME system stored procedure for renaming the column name:

Example 1: Rename Customer table column CustName to FullName using SP_RENAME

 

44. How to get month name from date in Sql Server

We can use DATENAME() function to get Month name from Date in Sql Server, here we need specify datepart parameter of the DATENAME function as month or mm or m all will return the same result.


 

45. How to get Day or Weekday name from date in Sql Server

We can use DATENAME() function to get Day/Weekday name from Date in Sql Server, here we need specify datepart parameter of the DATENAME function as weekday or dw both will return the same result.


 

46. How to find whether a Table is referenced by the Foreign Key constraint defined in another Table in Sql Server

We can use script like below to identify whether a Table is referenced by another Tables foreign key constraints in Sql Server:


 

47. How to Check if a String Contains a Sub-string in it in Sql Server

We can use the CHARINDEX() function to check whether a String contains a Sub-string in it. Name of this function is little confusing as name sounds something to do with character, but it basically returns the starting position of matched Substring in the main String. If it is not found then this function returns value 0. Below example demonstrates how we can use the CHARINDEX() function to check whether a String contains a Sub-string in it.


 

48. How to get all the records which contain double byte data in a particular NVARCHAR data type column Sql Server

In NVARCHAR DataType column we can store both Single byte and Double byte data. Many a times we want to know, how many records have double byte data in the NVARCHAR data type column. We can write a script like below for this:

 

49. How to get all the records which contain only single byte data in Sql Server

In NVARCHAR DataType column we can store both Single byte and Double byte data. Many a times we want to know, how many records have only single byte data in the NVARCHAR data type column. We can write a script like below for this:


 

50. How to get Date Part only from DateTime in Sql Server

There are multiple ways of getting date part only from DateTime, below is one such approach. 

 

Hope you liked this article and if any of your commonly used query is not listed here, please let me know. I will definitely post it and we can help together to the Sql Server Developers community.

Did you find apk for android? You can find new Free Android Games and apps.

Leave a Comment