Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.
----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%'
Reference : Pinal Dave (http://www.SQLAuthority.com)
167 Comments. Leave new
You always have exactly what I need.
Thanks!
good material
Nice article
Thanks!!!
how to create a procedure to rename all the tables in a data base..
Why do you need this?
i need to find a particular column used in diff stored proc in sql server 2005 database.how will i find all the stored proc names where i am using that particular column.
Try this
select routine_name from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE=’PROCEDURE’
and OBJECT_DEFINITION(object_id(‘routine_name’)) like ‘% col_name %’
I tryed this q. but it always gives me an empty result.
Need to find all columns in database where the column Createddate has ben used.
@Elena,
Can you please re-phrase your question. Do you want to get all Table / View information where the column createdDate has been used ?
— To get list of tables that has CreateDate column.
Select Table_Name, Column_Name
From Information_Schema.columns
Where Column_Name = ‘CreatedDate’
~Peace
hi,
really works great! thanks!
Thanks, this really work for me.
Very informative. This query was very useful…
Thanks a lot
Hi Pinal,
This query is great!
Was wondering can it be taken one step further and be used to rename a table in the DB.
eg. change table ‘Blah’ to ‘BlahBlahBlah’
Where this would update throughout the DB. Then i won’t have to manually change every object.
Thanks
declare @sql varchar(max)
set @sql=”
select @sql=@sql+’exec sp_rename ”’+table_name+”’,”’+table_name+’blah;”’ from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE=’BASE TABLE’
EXEC(@sql)
Great article, thanks! Just a quick extension of the example to tables referenced by all procedures and views follows
SET NOCOUNT ON
IF OBJECT_ID(‘tempdb..#work’) IS NOT NULL
DROP TABLE #work
CREATE TABLE #work
( name sysname
,xtype char(2)
,tablename sysname )
— List objects dependent on tables
DECLARE cur_tables CURSOR
READ_ONLY
FOR
SELECT name
FROM sysobjects
WHERE xtype = ‘u’
AND left(name,2) ‘dt’
DECLARE @name sysname
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @name
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
INSERT INTO #work
SELECT DISTINCT o.name ,o.xtype, @name as tablename
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE ‘%’ + @name + ‘%’
END
FETCH NEXT FROM cur_tables INTO @name
END
CLOSE cur_tables
DEALLOCATE cur_tables
select *
from #work
order by name
The query from Jens Johanneson is very usefull to me, thank you Jens
hi..
that’s a great query.. i am searching for this..
u r really a great man..
Dear Pinal
The query for finding the dependencies of a table is based on the syscomments table and the like operator , so it is giving results if the dbobject is in comments section or in a part of a sentence, which is inaccurate .
Can you give me a query for this problem.
Thank You.
Dileep.
Is there any system sp in sql server to get the create, insert or select script of a table instead of right clicking the table anme and selecting the particular query type(Create to, insert to or select to) as selecting from the menu takes long tme to fetch the query?
There is no any SP to do it
You have to rely on the SSMS
Would you please help me how i can find out Dependencies of views on stored procedure?
following query returns the dependencies of table but does not return the dependencies of views?
select distinct referenced_major_id from sys.sql_dependencies where
object_id = {0} //table object id
Use sp_depends stored procedure to get dependencies of any table or view as below:
sp_depends viewName
Regards,
Pinal Dave
Hi Friends,
I need your help, to get the dependency table list of the stored procedure.
USE dbA
Create Procedure usp_Test
As
BEGIN
If exists( select * from tblA)
SELECT * FROM tblB
END
When I execute, sp_depends usp_Test
It displays the tblB and tblA table lists
But when I call other database table names inside my procedure, it’s not showing the results, instead of it throws the error
‘Object does not reference any object, and no objects reference it.’
USE dbA
Create Procedure usp_Test
As
BEGIN
If exists( select * from dbB..tblC)
SELECT * FROM dbB..tblD
END
Could you please help me to get these tables (tblC and tblD)
I am using SQL Server 2005 not 2008 :-(
Manesh P
Hi, We will have upgradation from SQL 2000 to SQL 2005. During running of upgrade advisior I got this messgae :: “Outer join operators *= and =* are not supported in 90 compatibility mode”. I was wonder will it be possible for us to find this kind of query. Pinal your query really works but I can not search for views. So will you help me out?
Thanks
Sneha
This query was very useful to me..
thank u very much..
Nice query. Thanks a lot
This query is used by me today and it served my purpose. Thank you :)