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
write a procedure to calculate tax of emp as per following
anulal income tax
2,00,000 25%
Hi Pinal,
I have a question,
I have 50 instances in my production environment and i have a common database in all the instances.
My question is how can i find what are all the differences in the databases across all the instances like (tables, store Procedures, keys, constraints, functions…..etc).
How do i find the difference? Is there any script to find the differences.
Thanks,
Rahul.
Hi,
I have to go through about 1000 procedures and verify input parameters and the resultset and wonder if it possíble to query som magic table containing result sets for stored procedures. Input parameters are no problem but I have problems with getting the result set for each procedure. My plan is to create 2 databases (containing the versions I should compare) and run som sort of scripts identifying procedures that differs.
Do you want to verify if the results returned from two procedures are 100% correct?
Hi
What is different in sql server 2005 for BACKUP (compression to 2000 SQL SERVER )
hi, I am the beginner in sql server. I want to create Update Stored Procedure and how can i update one column in the table which contains multiple columns
Hi Dave
Here is my Q?
I have a stored Procedure called ‘xyz’. I want a query to display the name of the database containing that ‘xyz’ stored procedure.
Thanks a lot.
Refer this post
Hi Pinal,
I am facing a strange issue. On one of my tables data gets deleted automatically. Is there a way to find out which jobs are affecting the table?
Regards,
Priya
One option is to run a profiler and see
1. create a function that generates random alphanumeric code e.g ‘NFG6Y5’. the function should accept a number
specifying size of characters in the code.
2. create a table to store the codes and the code column should be unique.
3. create a stored proc that uses the function created to get codes and inserts them in the table created in the
above step. The stored proc should accept number of codes to generate, and size of each code.
using aspx c#,
1. create an application that allows users to log in, and using previous stored proc, allows user to view codes and
generate more.
Start with this
good one
Wow, this is something I’d always thought would be extremely useful at times, but I hadn’t had any idea that there was a query to get this information immediately. Works great. Thanks!
Hi Pinal,
You are really very good DBA.
Very nice blog.
You rock man. I always get a great solution from you.
Thanks a lot ,
Is it possible to use this query in a variable and then EXEC that variable like the following?
DECLARE @tableName NVARCHAR (800)
DECLARE @dbName NVARCHAR(800)
DECLARE @sql NVARCHAR(800)
SET @dbName = ‘TempDB’
SET @tableName = ‘Table1’
SET @sql = ‘SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.TEXT LIKE “%@tableName%”‘
EXEC (@sql)
Yes use like below
SET @sql = ‘SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.TEXT LIKE “%’+@tableName+’%”‘
Hi Pinal
there is a way that i can get all the name of SP ,View that is execute into other Sp.
how to get result of stored procedure into a table
Thanks Pinal for your post, this help me a lot.
—-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%’
will return all database objects which contains text ‘tablename’ that means if user adds comment which has the word ‘tablename’ but not depend upon the table ‘tablename’ will be returned which is not desired result, isn’t it?.
Thank you so much, you really saved my time
Wonderful.
Thanks
Deepa