472,787 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,787 developers and data experts.

Important SQL Server Statements/Searchs

111 100+
In this article I would like to share some of the important searches, like searching for table used in stored procedures and searching for list of tables and views used in a stored procedure.

1) List stored procedures which contain a table.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT syso.name FROM syscomments sysc INNER JOIN sysobjects syso ON sysc.id= syso.id WHERE sysc.TEXT LIKE '%tablename%' and upper(xtype)='P'

Here this query will return all the stored procedures using the given table.
Note: Keep your table name in place of: tablename in the above query.

What is syscomments object?.

The actual code for views, rules, defaults, triggers, CHECK constraints, DEFAULT constraints and stored procedures are stored in the syscomments table. The column TEXT in the syscomments table contains the actual code for all these objects. Knowing this allows you to write some simple T-SQL code that can scan the syscomments table looking for an actual table column name or database object name that we want to search.

2) List tables used in a stored procedure.
select name from sysobjects where id in

Expand|Select|Wrap|Line Numbers
  1. (select sd.depid from sysobjects so, sysdepends sd  
  2. where so.name = 'usp_Your_Stored_Procedure' and sd.id = so.id ) and upper(xtype) = 'U'
This statement will list all the tables used in the given stored procedure. (In our case its ''usp_Your_Stored_Procedure')

Note: This will return only the tables used in this procedure.

If you want to get the views also then you have to use below query.
Expand|Select|Wrap|Line Numbers
  1. select name from sysobjects where id in 
  2. (select sd.depid from sysobjects so, sysdepends sd  
  3. where so.name = 'usp_getRepReport_by_OU' and sd.id = so.id ) and upper(xtype) in ('U','V')
I hope this helps to debugging larger existing application where you stand as a support developer.

Bharath Reddy VasiReddy
Sr Software Engineer(RBC)
Jun 2 '10 #1
0 3208

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Yves Touze | last post by:
Hi All, I'm trying to migrate from SQL Server 7.0 to SQL Server 2000. I've got some ASP page which call VB components that retrieve shaped recordsets from SQL Server using the MSDATASHAPE...
by: Jeager | last post by:
Why is it, Microsoft manage to write operating systems and office applications with every bell and whistle facility known to man. Yet, even after years and years of development they still cannot...
by: meyvn77 | last post by:
Im using an ADP to connect to a SQL Sqever DB. In access it was really easy to say Inner join on table1 and table2 and update columnA from table1 with columnC from table2 where table1.key =...
by: (Pete Cresswell) | last post by:
I'd like to dabble in Terminal Server enough to see if I can get some of my MS Access apps to run through it. I've got the MSDN subscription. Is there enough there for a real-world...
by: Jeff | last post by:
For years I have been using VBA extensively for updating data to tables after processing. By this I mean if I had to do some intensive processing that resulted in data in temp tables, I would have...
by: smatta | last post by:
I have just installed MySql version 5.0-18 on Red Hat Fedora Core 4. It is running but I cant connect to it using MySql Query Browser running on my pc. >From my pc, I can telnet to the sql...
by: Rico | last post by:
I'm moving some queries out of an Access front end and creating views out of them in SQL Server 2005 express. In some of the numeric fields, I use nz quite often, ( i.e. nz(,0)) to return a zero...
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.