今天有一個同事問我, 他用 M$ 的 sp_tables 取得的 tables 包含了 VIEW, 他不想列出 view, 該怎麼做? 我跟他講, 可以直接存取 sys.tabes 裡的 name 欄位, 就可以取得該 database 裡所有的 table name.
資料來源:
Exploring your database schema with SQL
http://www.simple-talk.com/sql/t-sql-programming/exploring-your-database-schema-with-sql/
In the second part of Phil's series of articles on finding stuff (such as objects, scripts, entities, metadata) in SQL Server, he offers some scripts that should be handy for the developer faced with tracking down problem areas and potential weaknesses in a database.
Pretty quickly, if you are doing any serious database development, you will want to know more about a database than SSMS can tell you; there are just more things you might need to know than any one GUI can provide efficiently. For example, if you are doing a review of a development database, there are a number of facts you’ll need to establish regarding the database, its tables, keys and indexes, in order to home-in on any possible problem areas.
Fortunately, SQL Server provides any number of ways to get at the metadata you need. TheINFORMATION_SCHEMA views provide basic metadata about most of the entities in each database. The far-more-expansive Catalog views offer just about every piece of metadata that SQL Server currently exposes to the user.
This article provides various scripts for interrogating these views to get all sorts of useful information about your database that you would otherwise have to obtain slowly, click-by-wretched-click, from the sluggish SSMS Object browser. Once you’ve built up your own snippet or template library, you’ll find it very easy to access your databases’ metadata using SQL Code.
Interrogating Information Schema and Catalog Views
Codd’s fifth Rule (no. 4) of what comprises a relational database states that there must be an active online, inline, relational catalog that is accessible to authorized users by means of their regular query language. This means that users must be able to access the database's structure (catalog) using SQL. XQuery isn’t allowed by Codd’s rule; it must the same query language that they use to access the database's data. TheINFORMATION_SCHEMA provides a standard way of doing this for SQL-based relational databases.
Unfortunately, the standard doesn’t cover all the features in a SQL Server database. Sybase and SQL Server always provided the System tables to provide all the information that was required of a database’s structure, long before the INFORMATION_SCHEMA views became a SQL Standard. The Catalog Views, introduced in SQL Server 2005, provide a more efficient and concise way of doing this, even if one loses a bit of the feel for the underlying structure. There are many more views than actual system tables and Microsoft has been assiduous in providing simple ways of getting the metadata that you want.
Building a Snippet Library
If you are weaning yourself off dependency on the object browser of SQL Server Management Studio, you’ll need a clip library of handy routines instead. It is impossible to keep all the information in your head. I have a range of snippets, recipes and templates of SQL calls to get the information I want, many of which I present in this article. The SSMS templates are handy for this, though I’ll use SQL Prompt or AceText too, to store code snippets.
Probably my most-used snippet is one of the simplest, and it gets the actual definition of all the views, procedures and functions. This is something I keep as a template. You’ll have to change theMyObjectName for the name of the routine whose code you want.
--find the actual code for a particular stored procedure, view, function etc.
Select object_Name(object_ID),definition from sys.SQL_Modules
where object_Name(object_ID)='MyObjectName'
Sadly, it is impossible to get the build script for tables, along with all its associated objects, columns and indexes. It isn’t stored as such, though it is available via the Object Browser. If you want to get it via code, it has to be generated via SMO.
However, once you get started, there is a whole variety of things you will want to get information about what objects are associated with a given database, how many of them, who owns which objects, and so on.
Searching Schema-scoped Objects in a Database
Using a single Catalog view along with a special catalog function called OBJECTPROPERTY, we can find out the intimate details of any schema-scoped objects in the current database. Details of all schema-scoped objects are stored in the sys.objects Catalog view, from which other views such as sys.foreign_keys, sys.check_constraints, sys.tables and sys.views inherits. These additional views have added information that is specific to the particular type of object. There are database entities that are not classed as objects. Columns, indexes and parameters to routines, for example, aren't classed by SQL Server as objects.
The OBJECTPROPERTY function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.
Finding Tables with no Primary Keys
You’ll want to know if there tables without primary keys and why. Here is a way of getting that information from the INFORMATION_SCHEMA.tables view.
--Which of my tables don't have primary keys?
SELECT --we'll do it via information_Schema
TheTables.Table_Catalog+'.'+TheTables.Table_Schema+'.'
+TheTables.Table_Name AS [tables without primary keys]
FROM
information_Schema.tables TheTables
LEFT OUTER JOIN information_Schema.table_constraints TheConstraints
ON TheTables.table_Schema=TheConstraints.table_schema
AND TheTables.table_name=TheConstraints.table_name
AND constraint_type='PRIMARY KEY'
WHERE table_Type='BASE TABLE'
AND constraint_name IS NULL
ORDER BY [tables without primary keys]
The following code, using a Catalog view, should give the same result as the previous code, but much more easily. The TableHasPrimaryKey property of the OBJECTPROPERTY function simply returns 1 if a primary key exists, or 0 if not.
-- you can save a lot of code by using the catalog views
-- along with the OBJECTPROPERTY() function
Select
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'
+t.name as [tables without primary keys]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY [tables without primary keys]
Finding Tables with no Referential Constraints
You can, of course use almost the same query to explore many other characteristics of the tables. You’d certainly want to investigate any tables that seem to have no referential constraints, either as a key or a foreign reference.
--Which of my table are waifs (No Referential constraints)
SELECT
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Waif Tables]
FROM
sys.tables t
WHERE
OBJECTPROPERTY(object_id, 'TableHasForeignKey')=0
AND OBJECTPROPERTY(object_id, 'TableHasForeignRef')=0
AND OBJECTPROPERTY(object_id, 'IsUserTable')=1
ORDER BY
[Waif tables]
Finding Tables with no Indexes
You’d also be interested in those tables without clustered indexes and want to find out the reason why.
SELECT
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Tables without Clustered index]
FROM
sys.tables t
WHERE
OBJECTPROPERTY(object_id, 'TableHasClustIndex')=0
order by [Tables without Clustered index]
And you’d scratch your head a bit if there were tables of any great size without any index at all.
SELECT
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Tables without any index]
FROM
sys.tables t
WHERE
OBJECTPROPERTY(object_id, 'TableHasIndex')=0
order by [Tables without any index]
A one-stop View of your Table Structures
We can pull of this together in a single query against the sys.tables Catalog view to find out which objects (indexes, constraints and so on) do and don't exist on a given database. This is a handy query to get a summary of the characteristics of your tables’ structure at a quick glance.
SELECT
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'
+t.name AS [Qualified Name],
CASE WHEN OBJECTPROPERTY(object_id,'TableHasActiveFulltextIndex') = 0
THEN 'no' ELSE 'yes' END AS [FT index],--Table has an active full-text index.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasCheckCnst') = 0
THEN 'no' ELSE 'yes' END AS [Check Cnt],--Table has a CHECK constraint.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
THEN 'no' ELSE 'yes' END AS [Clustered ix],--Table has a clustered index.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasDefaultCnst') = 0
THEN 'no' ELSE 'yes' END AS [Default Cnt],--Table has a DEFAULT constraint.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasDeleteTrigger') = 0
THEN 'no' ELSE 'yes' END AS [Delete Tgr],--Table has a DELETE trigger.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasForeignKey') = 0
THEN 'no' ELSE 'yes' END AS [FK Cnt],--Table has a FOREIGN KEY constraint.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasForeignRef') = 0
THEN 'no' ELSE 'yes' END AS [FK Ref],--referenced by a FOREIGN KEY constraint.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasIdentity') = 0
THEN 'no' ELSE 'yes' END AS [Identity Col],--Table has an identity column.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasIndex') = 0
THEN 'no' ELSE 'yes' END AS [Any index],--Table has an index of any type.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasInsertTrigger') = 0
THEN 'no' ELSE 'yes' END AS [Insert Tgr],--Object has an INSERT trigger.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasNonclustIndex') = 0
THEN 'no' ELSE 'yes' END AS [nonCl Index],--Table has a nonclustered index.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
THEN 'no' ELSE 'yes' END AS [Primary Key],--Table has a primary key
CASE WHEN OBJECTPROPERTY(object_id,'TableHasRowGuidCol') = 0
THEN 'no' ELSE 'yes' END AS [ROWGUIDCOL],--ROWGUIDCOL for uniqueidentifier col
CASE WHEN OBJECTPROPERTY(object_id,'TableHasTextImage') = 0
THEN 'no' ELSE 'yes' END AS [Has Blob],--Table has text, ntext, or image column
CASE WHEN OBJECTPROPERTY(object_id,'TableHasTimestamp') = 0
THEN 'no' ELSE 'yes' END AS [Timestamp],--Table has a timestamp column.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasUniqueCnst') = 0
THEN 'no' ELSE 'yes' END AS [Unique Cnt],--Table has a UNIQUE constraint.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasUpdateTrigger') = 0
THEN 'no' ELSE 'yes' END AS [Update Tgr]--Table has an Update trigger.
FROM sys.tables t
ORDER BY [Qualified Name]
How many of each Object…
Since the OBJECTPROPERTY function generally returns either a 1 or a 0, it can be used pretty simply in order to find out not just whether there are constraints, defaults, rules or triggers on individual tables, but also how many of them there are.
--Which of my tables have constraints, defaults, rules or triggers on them? If so, then how many?
SELECT
DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+'.'+p.name AS[Qualified_Name],
Count(*),
sum(OBJECTPROPERTY ( s.object_ID , 'IsPrimaryKey')) as [Pk],
sum(OBJECTPROPERTY ( s.object_ID , 'IsCheckCnst')) as [ChkCns],
sum(OBJECTPROPERTY ( s.object_ID , 'IsDefaultCnst')) as [DefCns],
sum(OBJECTPROPERTY ( s.object_ID , 'IsForeignKey')) as [Fk],
sum(OBJECTPROPERTY ( s.object_ID , 'IsConstraint')) as [Cnstrnt],
sum(OBJECTPROPERTY ( s.object_ID , 'IsDefault')) as [Default],
sum(OBJECTPROPERTY ( s.object_ID , 'IsTrigger')) as [Trigger]
FROM
sys.objects S --to get the objects
inner JOIN sys.objects p
--to get the parent object so as to get the name of the table
ON s.parent_Object_ID=p.[object_ID]
WHERE
OBJECTPROPERTY ( p.object_ID , 'IsTable')<>0
GROUP BY
DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+'.'+p.name
Too many Indexes…
By a slightly different route, we can also find out which of our tables have the most indexes on them. Are any of them duplications? Here is a query you might use to see where the indexes might have gathered in undue numbers.
--Which of my tables have the most indexes?
SELECT TOP 10
COUNT(*) AS [Indexes],
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [table]
FROM
sys.indexes i
INNER JOIN sys.objects t
ON i.object_ID=t.object_ID
WHERE
USER_NAME(OBJECTPROPERTY(i.object_id, 'OwnerId')) NOT LIKE 'sys%'
GROUP BY
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name
ORDER BY
COUNT(*) DESC
Seeking out Troublesome Triggers
I find triggers particularly troublesome as it is not always obvious that they are there. I’m not the only developer who has spent an hour trying to work out why the result of an update is nothing like what one was expecting, only to be struck by the thought that some crazed code-jockey has inexplicably placed an update trigger on one of your tables. Yes, there it is. The following code should winkle out these lurking problems, and much more besides.
--Which of my tables have triggers on them, and how many?
SELECT --firstly, we'll search the names of the basic objects
DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+p.name AS [Qualified_Name],
COUNT(*) AS [how many]
FROM
sys.objects S --to get the objects
INNER JOIN sys.objects p
--to get the parent object so as to get the name of the table
ON s.parent_Object_ID=p.[object_ID]
WHERE
OBJECTPROPERTY ( s.object_ID , 'IsTrigger')<>0
and OBJECTPROPERTY ( p.object_ID , 'IsTable')<>0
GROUP BY
DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+p.name
.. and from this, you can drill down to see the sort of triggers your tables have:
SELECT
DB_NAME()+'.'+Object_Schema_name(t.[object_ID])+'.'+t.name AS[Qualified_Name],
case when OBJECTPROPERTY ( t.object_ID , 'HasAfterTrigger')<>0
then 'yes' else 'no' end as [After],
case when OBJECTPROPERTY ( t.object_ID , 'HasDeleteTrigger') <>0
then 'yes' else 'no' end as [Delete],
case when OBJECTPROPERTY ( t.object_ID , 'HasInsertTrigger') <>0
then 'yes' else 'no' end as [Insert],
case when OBJECTPROPERTY ( t.object_ID , 'HasInsteadOfTrigger') <>0
then 'yes' else 'no' end as [Instead Of],
case when OBJECTPROPERTY ( t.object_ID , 'HasUpdateTrigger ')<>0
then 'yes' else 'no' end as [Update]
FROM
sys.tables t
Querying the Documentation in Extended Properties
Catalog queries are a powerful way of querying the documentation in order to find out more about the business rules governing the database structure. There are several useful queries that you can use if you have been sensible enough to structure your documentation, such as listing out your procedures and functions, along with a brief synopsis of how they are used and why. Here, we’ll just restrict ourselves to a useful list of all the tables that have no documentation in the extended properties. There really aren’t any other places to put your table documentation so you can be fairly sure that these tables have no documentation.
--Which tables do not have any documentation in extended properties
SELECT
DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+'.'+s.name AS [Undocumented Table]
FROM
sys.objects s
LEFT OUTER JOIN sys.extended_properties ep
ON s.object_ID=ep.major_ID
AND minor_ID=0
WHERE
type_desc='USER_TABLE'
AND ep.value IS NULL
Object Permissions and Owners
There are a whole variety of things you will need information about as well as the details of the database objects; lists of permissions on each object and the type of permissions they represent, for example. Here is a query that lists the database-level permissions for the users (or particular user, if the final condition that is currently commented out is used.)
SELECT
CASE WHEN class_desc='DATABASE' THEN DB_NAME()
WHEN class_desc='SCHEMA' THEN SCHEMA_NAME(major_id)
WHEN class_desc='OBJECT_OR_COLUMN' THEN OBJECT_NAME(major_id)
WHEN class_desc='DATABASE_PRINCIPAL' THEN USER_NAME(major_id)
WHEN class_desc='TYPE' THEN TYPE_NAME(major_id)
ELSE 'Huh??'
END, USER_NAME(grantee_principal_id) AS grantee,
USER_NAME(grantor_principal_id) AS grantor, type, Permission_Name,
State_Desc
FROM
sys.database_permissions
WHERE
Class_Desc IN ('DATABASE', 'SCHEMA', 'OBJECT_OR_COLUMN',
'DATABASE_PRINCIPAL', 'TYPE')
-- and grantee_principal_id = DATABASE_PRINCIPAL_ID('public');
A different task is to explore the ownership of the various objects in your database. The following code will make this task a lot simpler.
--find the user names of all the objects
Select [Entity Type], [Owner name], [Object Name]
from
(
SELECT replace(SUBSTRING(v.name, 5, 31),'cns','constraint') AS [entity type]
,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS [owner name]
,DB_NAME()+'.'+Object_Schema_name(o.object_ID)+'.'+o.name as [Object Name]
FROM sys.objects o
LEFT OUTER JOIN master.dbo.spt_values v--to get the type of object
ON o.type = SUBSTRING(v.name, 1, 2) COLLATE database_default
AND v.type = 'O9T'
UNION
SELECT 'Type'
,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId'))
,DB_NAME()+'.'+Schema_name(schema_ID)+'.'+name
FROM sys.types
UNION
SELECT 'XML Schema Collection'
,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id))
,DB_NAME()+'.'+Schema_name(xsc.schema_ID)+'.'+xsc.name
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
ON s.schema_id = xsc.schema_id
)f
where [owner Name] not like 'sys'
What's been recently modified then?
If you are working with others on a database, then one of the more useful bits of code you can have is the following, which tells you the date at which your database objects were last-modified. This is the full code, but generally you'll modify it slightly as you'll just want to know the twenty latest modifications or so, or maybe list all the objects modified in the past week. Sadly, it will not tell you what has been deleted!
SELECT [Qualified_Name], Object_Type, CONVERT(CHAR(17), Created, 113),
CONVERT(CHAR(17), Last_Modified, 113)
FROM (SELECT --firstly, we'll search the names of the basic objects
DB_NAME()+'.'+Object_Schema_name(s.[object_ID])
+'.'+COALESCE(p.name+'.', '')+s.name
AS [Qualified_Name],
REPLACE(SUBSTRING(v.name, 5, 31), 'cns', 'constraint')+' name'
AS Object_Type, s.create_date AS 'Created',
s.modify_date AS 'Last_Modified'
FROM sys.objects S --to get the objects
LEFT OUTER JOIN master.dbo.spt_values v --to get the type of object
ON s.type=SUBSTRING(v.name, 1, 2) COLLATE database_default
AND v.type='O9T'
LEFT OUTER JOIN sys.objects p --to get any parent object
ON s.parent_Object_ID=p.[object_ID]
WHERE Object_Schema_name(s.object_ID) NOT LIKE 'sys%'
UNION ALL --now search the XML schema collection names
SELECT DB_NAME()+'.'+name, 'XML Schema Collection name',
create_date AS 'created', modify_date AS 'Last Modified'
FROM sys.xml_schema_collections
UNION ALL
SELECT DB_NAME()+'.'+name, LOWER(type_desc) COLLATEdatabase_default,
create_date AS 'created', modify_date AS 'Last Modified'
FROM sys.triggers
WHERE parent_class=0--only DDL triggers
UNION ALL --names of CLR assemblies
SELECT DB_NAME()+'.'+name, 'CLR Assembly', create_date AS 'created',
modify_date AS 'Last Modified'
FROM sys.assemblies
UNION ALL --almost done. We do the agent jobs too here
SELECT DISTINCT
'Agent'+'.'+DB_NAME()+'.'+[name] COLLATE database_default,
'Agent Job', date_created, date_modified
FROM MSDB.dbo.sysJobs Job
INNER JOIN MSDB.dbo.sysJobSteps Step ON Job.Job_Id=Step.Job_Id
WHERE Database_name LIKE DB_NAME() COLLATE database_default)objects
ORDER BY Last_Modified DESC
Searching all your Databases
You can use these various routines on all databases, or on a list of databases. You can use undocumented code, of course, but a better approach would be to use yet another system catalog calledsys.Databases. You can then execute the code against all databases, collecting the result into a single table. Here is an example:
DECLARE @ii
INT, --loop counter
@iiMax INT, --loop counter upper limit
@CurrentDatabase VARCHAR(255), --variable holding name of current database
@command NVARCHAR(2000)--the dynamic command
DECLARE @whatWeSearch TABLE --the table of all the databases we search
(Database_ID INT IDENTITY(1, 1),
DatabaseName VARCHAR(255)
)
DECLARE @Result TABLE --the result
([Tables Without Primary Keys] VARCHAR(255)
)
INSERT INTO @whatWeSearch (DatabaseName)
SELECT name FROM sys.Databases
WHERE name NOT IN ('Master', 'TempDB', 'Model', 'MSDB')
--get all the databases we want to search
SELECT @ii=MIN(Database_ID), @iiMax=MAX(Database_ID) FROM @whatWeSearch
--and do them all one after another
WHILE @ii<=@iiMax
BEGIN
SELECT @CurrentDatabase=QUOTENAME(DatabaseName)
FROM @whatWeSearch WHERE Database_ID=@ii
SET @Command=N'Use '+@CurrentDatabase+'
Select DB_NAME()+''.''+Object_Schema_name(t.object_ID)+''.''
+t.name as [tables without primary keys]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id,''TableHasPrimaryKey'') = 0
ORDER BY [tables without primary keys]'
INSERT INTO @Result ([Tables Without Primary Keys])
EXEC sp_executesql @Command
SELECT @ii=@ii+1 --and on to the next database
END
SELECT [Tables Without Primary Keys] FROM @Result
Interrogating Object Dependencies
If you are faced with the difficult task of refactoring code whilst keeping everything running reliably, one of the most useful things you can determine is the chain of dependencies of database objects. You’ll particularly need this if you are considering renaming anything in the database, changing a column in a table, moving a module, or are replacing a data type. Unfortunately, it isn’t particularly reliable.
One problem that SQL Server faces is that some entities used in an application can contain caller-dependent references, or one-part name references (e.g. they don’t specify the Schema). This can cause all sorts of problems because the binding of the referenced entity depends on the schema of the caller and so the reference cannot be determined until the code is run. Additionally, if code is stored in a string and executed, then the entities that the code is referencing cannot be recorded in the metadata.
One thing you can do, if you are checking on the dependencies of a routine (non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger) is to update its metadata. This is because the metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects. This is done by usingsys.sp_refreshsqlmodule, e.g.
sys.sp_refreshsqlmodule 'dbo.ufnGetContactInformation'
Even then, the information you get back from the metadata about dependencies is to be taken with a pinch of salt. It is reasonably easy to get a list of what objects refer to a particular object, and what objects are referred to by an object. Variations of the following query will do it for you, using the SQL Server 2005 catalog view sys.sql_dependencies.
--list all the dependencies in the database. Normally you'll have a WHERE clausee
--to pick just the object you want.
SELECT
Object_Schema_name(object_id)+'.'+COALESCE(OBJECT_NAME(object_id), 'unknown')
+COALESCE('.'+ COL_NAME(object_id, column_id), '') AS [referencer],
Object_Schema_name(referenced_major_id)+'.'+OBJECT_NAME(referenced_major_id)
+COALESCE('.'+COL_NAME(referenced_major_id, referenced_minor_id), '') AS[Referenced]
FROM
sys.sql_dependencies
WHERE
class IN (0, 1) --AND referenced_major_id = OBJECT_ID('HumanResources.Employee')
ORDER BY
COALESCE(OBJECT_NAME(object_id), 'x'),
COALESCE(COL_NAME(object_id, column_id), 'a')
You will have spotted that what you often need is not limited to the dependent objects of the object you are re-engineering. If you are altering the behavior of the object, you will need to then need to look in turn at the objects that are dependent on these dependent objects, and so on (and watch out for mutual dependency!). In other words, you need the dependency chains.
ALTER FUNCTION DependencyChainOf (@Object_Name VARCHAR(200))
/**
summary: >
The DependencyChainOf function takes as a parameter either a table
view, function or procedure name or a column name. It works best
with the full object name of schema.object(.column). returns a
table that gives the dependency chain with both forward and
backward links so that you can see what objects are likely to be
affected by the changes you make, and what objects your object
is referencing..
Revisions:
- version: 1
Modification: Created Table-balued function
Author: Phil Factor
Date: 01/03/2010
- version: 2
Modification: added catch for mutual dependency
Author: Phil Factor
Date: 02/03/2010
example:
- code:
Select distinct * from DependencyChainOf('VEmployee')
order by The_level,TheName
- code:
EXEC sys.sp_refreshsqlmodule 'MyProc1'
Select distinct * from DependencyChainOf('MyTable')
order by y y y y y y The_level,TheName
**/
RETURNS @Referenced TABLE
(
TheName VARCHAR(200), The_Object_ID BIGINT, Column_ID INT,
Class INT, The_Level INT
)
AS
BEGIN
--identify the object or column
--get the referencing entity
INSERT INTO
@referenced (The_Object_ID, Column_ID, Class, The_Level)
SELECT TOP 1
object_ID, Column_ID, class, 1
FROM
(SELECT
Object_Schema_name(object_id)+'.'+COALESCE(OBJECT_NAME(object_id),'unknown')
+COALESCE('.'+COL_NAME(object_id, column_id), '') AS [name],d.object_ID,
d.column_ID, class
FROM sys.sql_dependencies d
) names
WHERE
CHARINDEX(REVERSE(@Object_Name), REVERSE(names.name))=1
OR OBJECT_NAME([Object_ID])=@Object_Name
IF NOT EXISTS ( SELECT 1 FROM @referenced )
INSERT INTO
@referenced (The_Object_ID, Column_ID, Class, The_Level)
SELECT TOP 1 object_ID, Column_ID, class, 1
FROM
(SELECT
Object_Schema_name(referenced_major_id)+'.'+OBJECT_NAME(referenced_major_id)
+COALESCE('.'+COL_NAME(referenced_major_id,referenced_minor_id), '') AS [name],
d.Referenced_Major_ID AS [object_ID],
d.Referenced_Minor_ID AS [column_ID], class
FROM
sys.sql_dependencies d
) names
WHERE
CHARINDEX(REVERSE(@Object_Name), REVERSE(names.name))=1
OR OBJECT_NAME([Object_ID])=@Object_Name
DECLARE @Currentlevel INT, @RowCount INT
SELECT @Currentlevel=1, @Rowcount=1
WHILE @Rowcount>0 AND @currentLevel<50--guard against mutual dependency
BEGIN
INSERT INTO @referenced (The_Object_ID, Column_ID, Class, The_Level)
SELECT Referenced_Major_ID, Referenced_Minor_ID, d.class, The_Level+1
FROM @referenced r
INNER JOIN sys.sql_dependencies d
ON The_Object_ID=object_ID
--AND r.column_ID=d.Column_ID
AND r.class=d.Class
AND @Currentlevel=The_Level
SELECT @rowcount=@@Rowcount, @CurrentLevel=@CurrentLevel+1
END
SELECT @Currentlevel=1, @Rowcount=1
WHILE @Rowcount>0 AND @currentLevel>-50--guard against mutual dependency
BEGIN
INSERT INTO @referenced (The_Object_ID, Column_ID, Class, The_Level)
SELECT Object_ID, d.column_ID, d.class, The_Level-1
FROM
@referenced r
INNER JOIN sys.sql_dependencies d
ON The_Object_ID=Referenced_Major_ID
--AND r.column_ID=d.Referenced_Major_ID
AND r.class=d.Class
AND @Currentlevel=The_Level
SELECT
@rowcount=@@Rowcount, @CurrentLevel=@CurrentLevel-1
END
UPDATE @Referenced SET TheName=
DB_NAME()+'.'+Object_Schema_name(The_object_ID)+'.'+OBJECT_NAME(The_object_ID)
+COALESCE('.'+COL_NAME(The_object_ID, column_id), '')
RETURN
END
It's worth noting that in SQL Server 2008, you would use the sys.sql_expression_dependenciestable, which has a much improved way of working out dependencies. There is a very full discussion, with example code, here at Reporting SQL Dependencies.
Summary
With the various scripts, suggestions and illustrations in this article, I hope I’ve given you a taste for using the Catalog, or Information Schema, views for getting all sorts of useful information about the objects in your databases, and of the dependencies that exist between.
Some of this information is available from the SSMS Object browser but it is slow going. Once you’ve built up your own snippet or template library, you’ll find it quicker and easier to take the Spartan approach, and search your databases’ catalog using SQL.
