Query System Tables
SQL Server's syscomments
table stores the original SQL definition statement for every view, rule, default, trigger, CHECK
constraint, and stored procedure in your database. That's a lot of information! You can query this table to list dependent objects using a SQL statement in the following form:
SELECT DISTINCT sysobj.name
INNER JOIN sysobjects sysobj ON syscomments.id = sysobj.id
WHERE charindex(object, text) > 0
WHERE object is a string value that represents the object on which you're checking for dependencies.
This query displays the names of objects where the id value in syscomments and sysobjects match and object is in the text column. The text column in syscomments contains the actual text for SQL definition statements. Figure 2 shows the results of running this query against the Employee table. As you can see, the query lists views, user-defined functions, and stored procedures but not tables. You can use this to your advantage. In general, one thinks of tables as being related, but not necessarily dependent. If you want to omit tables from the list, use this query.
|Figure 2. Dependent Object Results from Query of System Tables: You can query system tables to find dependent objects.|
SQL Server 2000 included syscomments for backward compatibility only. SQL Server 2008 stores SQL expressions in Catalog metadata. Specifically, 2008's sys.sql_modules Catalog view replaces syscomments. In addition, the query method doesn't identify the dependent object by type.
Execute a Built-In Routine
SQL Server offers a bounty of schema views that retrieve information from the system tables. (These views conform to SQL-92 standard.) Based on the sysobjects and syscolumns system tables, INFORMATION_SCHEMA.ROUTINES returns dependency information using the following syntax:
SELECT routine_name, routine_type
Where routine_definition LIKE '%object%'
The routine_definition column contains the source statements for functions, stored procedures, and triggers. Figure 3 shows the results of checking dependencies for the Employee table. This view displays user-defined functions and stored procedures, and it identifies them by type, but it doesn't list tables or views. This isn't necessarily a limitation; use this view when you want to omit tables and views. Because these statements often contain embedded carriage returns, you should allow for them in the formatting of the results (if it matters).
|Figure 3. Schema View to Return Dependencies: A schema view can return dependent functions, stored procedures, and triggers.|
This view has one limitation you should know about: routine_definition, which returns the first 4,000 characters of the definition statement. That means this method has the potential to be incomplete if a statement contains more than 4,000 characters. That's not going to be a factor for most database developers, but you should know about it.