Click here to check my latest exciting videos on youtube
Search Mallstuffs

Flag Counter
Spirituality, Knowledge and Entertainment


Locations of visitors to this page


Latest Articles


Move to top
Get list of all database objects with table name or column name
Posted By Sarin on Dec 04, 2012     RSS Feeds     Latest Hinduism news
3538 Views

While working on a databse, I frequently get the need of retrieivng list of all stored procedures, triggers, or function that uses a particular column or particular table. I have collected list of all such queries which will help us to find databse objects using a specific column or specific table.

To get list of all tables with a specific table name, use anyone of the below following queries

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

To get list of all procedures using a specific column or a specific table, any one of the above query would work. In addition, you can use the below queries to do the same
----Option 1
select [Name]    
from sys.procedures
where object_definition([object_id]) like '%YourColumnName%';
  
----Option 2
select OBJECT_NAME(object_id)  
from sys.sql_modules
where  OBJECTPROPERTY(object_id,'IsProcedure') = 1 and  
    definition like '% YourColumnName  or YourTableName %'
use the below query to get list of all objects along with its type
 
Select distinct sysobjects.name
, case  
  when sysobjects.type = 'TR' then 'TRIGGER'  
  when sysobjects.type = 'P' then 'PROCEDURE'  
  when sysobjects.type = 'V' then 'VIEW'  
  else 'UNKNOWN' end type
from sysobjects inner join syscomments
on sysobjects.id = syscomments.id
where syscomments.text like '% YourColumnName  or YourTableName %'
All the above queries will work fine for small databses. But as the database tables increases, number of columns having the same name will also increase. In suh cases, above query will return many results out of which you have to filter out the required one manually. Below query will avoid such manual work by letting you define the column name along with the table name

DECLARE @Schema SYSNAME
DECLARE @Table SYSNAME
DECLARE @Column SYSNAME
SET @Schema = 'dbo'
SET @Table = 'TableName'
SET @Column = 'ColumnName'
SELECT o.name
FROM sys.sql_dependencies AS d
  INNER JOIN sys.all_objects AS o ON o.object_id = d.object_id
  INNER JOIN sys.all_objects AS ro ON ro.object_id = d.referenced_major_id
  INNER JOIN sys.all_columns AS c ON c.object_id = ro.object_id AND c.column_id = d.referenced_minor_id
WHERE (SCHEMA_NAME(ro.schema_id)=@Schema)  
  and o.type_desc = 'SQL_STORED_PROCEDURE'
  and ro.name = @Table
  and c.name = @Column
GROUP BY o.name
  
As shown in the screenshot below, I queried my database to retrieve list of all SP’s referencing column ‘blogid’ of table ‘blogs’


  
Hope this Helps.
Note: Images used on this website are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon Foundation or were found in google search under "Free to use and share". If any of the images presented here violates copyright issues or infringes anyone copyright or are not under "Fair use", then please bring it to our notice. Read Disclaimer for more.

Share this to your friends. One of your friend is waiting for your share.
Related Articles
Working with silver light datagrid
Different types of alter table command to change database schema
Changing Grid View header and footer at run time
Get All Tables in a Database
Loop through all records in Sql server with while statement
Get comma separated result of a database query
Call Codebehind method from GridView ItemTemplate Eval function
Get list of all database objects with table name or column name
showing row details on button click of silver light datagrid
Working with Grid control in silverlight

Post Comment