Find columns across database in SQL Server

Best way to find a column referenced in different tables in database using SQL Server

select t.name from sys.columns c

inner join sys.tables t

on c.object_id = t.object_id

where c.name = '<Column Name>'

Find all tables without Primary Keys

Find all tables without Primary Keys

This is very simple but effective script. It list all the table without primary keys.


SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableNameFROM sys.tablesWHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0ORDER BY SchemaName, TableName;GO


Thats all. Happy Querying :).

Find given text in all stored procedures in SQL Server

Recently, we were working on requirement and need to search a text in list of Stored Procedures. The funny thing, that stored procedure list is more than 2000.  So, I started googling, and find following ways.

1) 

SELECT 
       OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%TEXT%'

2)

Select object_name(object_id), definition
From sys.sql_modules

Where definition like '%TEXT%' and objectpropertyex(object_id, 'isProcedure')=1


e.g. Lets take example, you need to search 100 in all stored procedure. Use following query.

1)  SELECT 
       OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%100%'


2)

Select object_name(object_id), definition
From sys.sql_modules

Where definition like '%100%' and objectpropertyex(object_id, 'isProcedure')=1

That's it. You will get results matching criteria. Just Enjoy. Happy coding.