Some Useful And Tricky Queries

Today, I am going to share some useful and tricky SQL queries. These have helped me a lot while programming. So, I decided to share my experience over the network.

Find tables with column name

Description

Sometimes, I have found myself in a situation where I have had to work with an estimate,  for example, I know the column name and wanted to find the tables that have matching column names. Suppose I know that the column name is "Order" and I want to find the tables that have that kind of column in our database. So, for that, we can use a query like this:

Query
  1. Select T.Name as [Table Name] , C.Name as [Column Namefrom sys.tables T inner join sys.columns C on T.object_id=C.object_id where C.Name like '%Order%'  
That is going to return the names of tables that contain the column name "Order".

Find tables that don't have indexes

Description

Suppose you have a huge database, say, 1TB, having tables in the thousands, and you want to enhance the speed of execution and want to add indexes on non-indexed tables.

Query
  1. SELECT Name 'Tables without any Indexes' FROM SYS.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0  
This returns the name of tables that don't have indexes.

Find dependent objects on a table

Description

It is always a good practice to find the dependent objects on a table before making any update on that specific table.

Query

sp_depends 'TableName'

That will return the dependent objects (like Stored procedures, Views etc.) on that table.

Get result set concatenated with comma (or any other delimiters)

Description

This one is very interesting and useful for us. Suppose, we want to get ids separated by commas from tables so that we could use that further, either in delete operations or in an update operation.

Query
  1. DECLARE @ConcatData VARCHAR(MAX)   
  2. SELECT@ConcatData=COALESCE(@ConcatData+',','')+CONVERT(VARCHAR(40),ColumnToConcat) FROM Table1    
  3. SELECT @ConcatData    
Note

CONVERT(VARCHAR(40), ColumnToConcat) - in this statement, conversion is an option but it's safe to do if you are concatenating UNIQUEIDENTIFIER column.

This will return us the result set separated by commas.

So that's all for now. This is my first blog so please give your feedback in the comments section. Your comments will motivate me to write more blogs.

Have a great coding day!!