SQL Hacks

These are some script hacks that can help you.


This is the script when you need to checkout that how many store procedure use this table.


SELECT distinct t.TABLE_NAME, s.ROUTINE_NAME,s.SPECIFIC_SCHEMA
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.ROUTINES s
ON s.ROUTINE_NAME IN
(
        SELECT referencing_entity_name
        FROM sys.dm_sql_referencing_entities('dbo.abc_tablename', 'OBJECT')
)
AND s.ROUTINE_TYPE = 'PROCEDURE'
WHERE t.TABLE_TYPE = 'BASE TABLE'
and table_name='abc_tablename'


-- this query duplicate the item for example if  the same Store procedure use this table 2 time it will show same Store procedure name 2 times

SELECT OBJECT_NAME(id) ObjectName , [Text]
FROM syscomments
WHERE TEXT LIKE '%abc_tablename%'




This is the script when you do not know exactly the table name and some body tell you go to this table that some name like this .. So this query will help you out

SELECT *
FROM sysobjects
WHERE name LIKE '%abc_tablename%' and xtype='U'



This is the script when you need to checkout that how many table use this column name

select * from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%user%'
order by TABLE_NAME



This script give the all columns name for that table.There is also short-cut select table name and hit alt+f1
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='abc_tablename'