{"id":821,"date":"2010-03-02T00:00:00","date_gmt":"2010-03-02T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/exploring-your-database-schema-with-sql\/"},"modified":"2021-09-29T16:22:00","modified_gmt":"2021-09-29T16:22:00","slug":"exploring-your-database-schema-with-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/exploring-your-database-schema-with-sql\/","title":{"rendered":"Exploring your database schema with SQL"},"content":{"rendered":"
\n

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.<\/p>\n

Fortunately, SQL Server provides any number of ways to get at the metadata you need. The INFORMATION_SCHEMA<\/code> views provide basic metadata about most of the entities in each database. The far-more-expansive Catalog<\/b> views offer just about every piece of metadata that SQL Server currently exposes to the user.<\/p>\n

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.<\/p>\n

Interrogating Information Schema and Catalog Views<\/h2>\n

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. The INFORMATION_SCHEMA<\/code>  provides a standard way of doing this for SQL-based relational databases.<\/p>\n

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<\/code> 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.<\/p>\n

Building a Snippet Library<\/h2>\n

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.<\/p>\n

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 the MyObjectName<\/code> for the name of the routine whose code you want.<\/p>\n

--find the actual code for a particular stored procedure, view, function etc.\r\nSELECT OBJECT_NAME(object_ID), definition\r\n  FROM sys.SQL_Modules\r\n  WHERE OBJECT_NAME(object_ID) = 'MyObjectName';\r\n<\/pre>\n
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.<\/p>\n
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.<\/p>\n
Searching Schema-scoped Objects in a Database<\/h2>\n
Using a single Catalog view along with a special catalog function called OBJECTPROPERTY<\/code>, 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<\/code> Catalog view, from which other views such as  sys.foreign_keys, sys.check_constraints, sys.tables and sys.views <\/code> 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.<\/p>\n
The OBJECTPROPERTY<\/code> function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.<\/p>\n
Finding Tables with no Primary Keys<\/h3>\n
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<\/code> view.<\/p>\n
--Which of my tables don't have primary keys?\r\nSELECT --we'll do it via information_Schema\r\n  TheTables.Table_Catalog+'.'+TheTables.Table_Schema+'.'\r\n                        +TheTables.Table_Name AS [tables without primary keys]\r\nFROM\r\n  information_Schema.tables TheTables\r\n  LEFT OUTER JOIN information_Schema.table_constraints TheConstraints\r\n    ON TheTables.table_Schema=TheConstraints.table_schema\r\n       AND TheTables.table_name=TheConstraints.table_name\r\n       AND constraint_type='PRIMARY KEY'\r\nWHERE table_Type='BASE TABLE'\r\n  AND constraint_name IS NULL\r\nORDER BY [tables without primary keys]\r\n<\/pre>\n
\"962-NoPrimaryKey.jpg\"<\/p>\n
The following code, using a Catalog view, should give the same result as the previous code, but much more easily. The TableHasPrimaryKey<\/code> property of the OBJECTPROPERTY<\/code> function simply returns 1 if a primary key exists, or 0 if not.<\/p>\n
-- you can save a lot of code by using the catalog views\r\n-- along with the OBJECTPROPERTY() function\r\nSelect \r\nDB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'\r\n                           +t.name  as [tables without primary keys]\r\nFROM sys.tables t\r\nWHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0\r\nORDER BY [tables without primary keys]\r\n<\/pre>\n
Finding Tables with no Referential Constraints<\/h3>\n
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.<\/p>\n
            \r\n--Which of my table are waifs (No Referential constraints)\r\nSELECT\r\n  DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Waif Tables]\r\nFROM\r\n  sys.tables t\r\nWHERE\r\n  OBJECTPROPERTY(object_id, 'TableHasForeignKey')=0\r\n  AND OBJECTPROPERTY(object_id, 'TableHasForeignRef')=0\r\n  AND OBJECTPROPERTY(object_id, 'IsUserTable')=1\r\nORDER BY\r\n  [Waif tables]\r\n<\/pre>\n
\"962-Waif2.jpg\"<\/p>\n
Finding Tables with no Indexes<\/h3>\n
You’d also be interested in those tables without clustered indexes and want to find out the reason why.<\/p>\n
SELECT\r\n  DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Tables without Clustered index]\r\nFROM\r\n  sys.tables t\r\nWHERE\r\n  OBJECTPROPERTY(object_id, 'TableHasClustIndex')=0\r\norder by [Tables without Clustered index]  \r\n   \r\n<\/pre>\n
And you’d scratch your head a bit if there were tables of any great size without any index at all.<\/p>\n
SELECT\r\n  DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Tables without any index]\r\nFROM\r\n  sys.tables t\r\nWHERE\r\n  OBJECTPROPERTY(object_id, 'TableHasIndex')=0\r\norder by [Tables without any index]  \r\n<\/pre>\n
\"962-TablesWithoutInexes4.jpg\"<\/p>\n
A one-stop View of your Table Structures<\/h2>\n
We can pull of this together in a single query against the sys.tables<\/code> 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.<\/p>\n
SELECT \r\nDB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'\r\n                                                    +t.name  AS [Qualified Name],\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasActiveFulltextIndex') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [FT index],--Table has an active full-text index.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasCheckCnst') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Check Cnt],--Table has a CHECK constraint.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Clustered ix],--Table has a clustered index.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasDefaultCnst') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Default Cnt],--Table has a DEFAULT constraint.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasDeleteTrigger') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Delete Tgr],--Table has a DELETE trigger.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasForeignKey') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [FK Cnt],--Table has a FOREIGN KEY constraint.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasForeignRef') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [FK Ref],--referenced by a FOREIGN KEY constraint.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasIdentity') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Identity Col],--Table has an identity column.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasIndex') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Any index],--Table has an index of any type.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasInsertTrigger') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Insert Tgr],--Object has an INSERT trigger.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasNonclustIndex') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [nonCl Index],--Table has a nonclustered index.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Primary Key],--Table has a primary key\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasRowGuidCol') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [ROWGUIDCOL],--ROWGUIDCOL for uniqueidentifier col\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasTextImage') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Has Blob],--Table has text, ntext, or image column\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasTimestamp') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Timestamp],--Table has a timestamp column.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasUniqueCnst') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Unique Cnt],--Table has a UNIQUE constraint.\r\n  CASE WHEN OBJECTPROPERTY(object_id,'TableHasUpdateTrigger') = 0  \r\n       THEN 'no' ELSE 'yes' END AS  [Update Tgr]--Table has an Update trigger. \r\nFROM sys.tables t\r\nORDER BY [Qualified Name]\r\n<\/pre>\n
\"962-TableCharacteristics8.jpg\"<\/p>\n
How many of each Object…<\/h3>\n
Since the OBJECTPROPERTY<\/code> 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.<\/p>\n
--Which of my tables have constraints, defaults, rules or triggers on them? If so, then how many?  SELECT \r\n  DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+'.'+p.name AS [Qualified_Name],\r\n  Count(*),\r\n  sum(OBJECTPROPERTY ( s.object_ID , 'IsPrimaryKey')) as [Pk],\r\n  sum(OBJECTPROPERTY ( s.object_ID , 'IsCheckCnst')) as [ChkCns],\r\n  sum(OBJECTPROPERTY ( s.object_ID , 'IsDefaultCnst')) as [DefCns],\r\n  sum(OBJECTPROPERTY ( s.object_ID , 'IsForeignKey')) as [Fk],\r\n  sum(OBJECTPROPERTY ( s.object_ID , 'IsConstraint')) as [Cnstrnt],\r\n  sum(OBJECTPROPERTY ( s.object_ID , 'IsDefault')) as [Default],\r\n  sum(OBJECTPROPERTY ( s.object_ID , 'IsTrigger')) as [Trigger]\r\n  \r\nFROM\r\n  sys.objects S --to get the objects\r\n  inner JOIN sys.objects p\r\n    --to get the parent object so as to get the name of the table\r\n    ON s.parent_Object_ID=p.[object_ID]\r\nWHERE\r\n  OBJECTPROPERTY ( p.object_ID , 'IsTable')<>0\r\nGROUP BY\r\n  DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+'.'+p.name\r\n<\/pre>\n
\"962-Constraints13.jpg\"<\/p>\n
Too many Indexes…<\/h3>\n
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.<\/p>\n
--Which of my tables have the most indexes?\r\nSELECT TOP 10\r\n  COUNT(*) AS [Indexes],\r\n  DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [table]\r\nFROM\r\n  sys.indexes i\r\n  INNER JOIN sys.objects t\r\n    ON i.object_ID=t.object_ID\r\nWHERE\r\n  USER_NAME(OBJECTPROPERTY(i.object_id, 'OwnerId')) NOT LIKE 'sys%'\r\nGROUP BY\r\n  DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name\r\nORDER BY\r\n  COUNT(*) DESC\r\n<\/pre>\n
\"962-NumIndexes9.jpg\"<\/p>\n
Seeking out Troublesome Triggers<\/h2>\n
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.<\/p>\n
--Which of my tables have triggers on them, and how many?  SELECT --firstly, we'll search the names of the basic objects\r\n  DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+p.name AS [Qualified_Name],\r\n  COUNT(*) AS [how many]\r\nFROM\r\n  sys.objects S --to get the objects\r\n  INNER JOIN sys.objects p\r\n    --to get the parent object so as to get the name of the table\r\n    ON s.parent_Object_ID=p.[object_ID]\r\nWHERE\r\n  OBJECTPROPERTY ( s.object_ID , 'IsTrigger')<>0\r\n  and OBJECTPROPERTY ( p.object_ID , 'IsTable')<>0\r\nGROUP BY\r\n  DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+p.name\r\n<\/pre>\n
.. and from this, you can drill down to  see the sort of triggers your tables have:<\/p>\n
SELECT   DB_NAME()+'.'+Object_Schema_name(t.[object_ID])+'.'+t.name AS [Qualified_Name],\r\n  case when OBJECTPROPERTY ( t.object_ID , 'HasAfterTrigger')<>0 \r\n                                         then 'yes' else 'no' end as [After],\r\n  case when OBJECTPROPERTY ( t.object_ID , 'HasDeleteTrigger') <>0 \r\n                                         then 'yes' else 'no' end as  [Delete],\r\n  case when OBJECTPROPERTY ( t.object_ID , 'HasInsertTrigger') <>0 \r\n                                         then 'yes' else 'no' end as  [Insert],\r\n  case when OBJECTPROPERTY ( t.object_ID , 'HasInsteadOfTrigger') <>0 \r\n                                         then 'yes' else 'no' end as [Instead Of],\r\n  case when OBJECTPROPERTY ( t.object_ID , 'HasUpdateTrigger ')<>0 \r\n                                         then 'yes' else 'no' end as [Update]\r\n FROM\r\n sys.tables t\r\n<\/pre>\n
\"962-Triggers15.jpg\"<\/p>\n
Querying the Documentation in Extended Properties<\/h2>\n
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.<\/p>\n
--Which tables do not have any documentation in extended properties\r\nSELECT\r\n  DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+'.'+s.name AS [Undocumented Table]\r\nFROM\r\n  sys.objects s\r\n  LEFT OUTER JOIN sys.extended_properties ep\r\n    ON s.object_ID=ep.major_ID\r\n       AND minor_ID=0\r\nWHERE\r\n  type_desc='USER_TABLE'\r\n  AND ep.value IS NULL\r\n<\/pre>\n
Object Permissions and Owners<\/h2>\n
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.)<\/p>\n
 SELECT\r\n  CASE WHEN class_desc='DATABASE' THEN DB_NAME()\r\n       WHEN class_desc='SCHEMA' THEN SCHEMA_NAME(major_id)\r\n       WHEN class_desc='OBJECT_OR_COLUMN' THEN OBJECT_NAME(major_id)\r\n       WHEN class_desc='DATABASE_PRINCIPAL' THEN USER_NAME(major_id)\r\n       WHEN class_desc='TYPE' THEN TYPE_NAME(major_id)\r\n       ELSE 'Huh??'\r\n  END, USER_NAME(grantee_principal_id) AS grantee,\r\n  USER_NAME(grantor_principal_id) AS grantor, type, Permission_Name,\r\n  State_Desc\r\nFROM\r\n  sys.database_permissions\r\nWHERE\r\n  Class_Desc IN ('DATABASE', 'SCHEMA', 'OBJECT_OR_COLUMN',\r\n                 'DATABASE_PRINCIPAL', 'TYPE')\r\n-- and grantee_principal_id = DATABASE_PRINCIPAL_ID('public');\r\n \r\n<\/pre>\n
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. <\/p>\n
--find the user names of all the objects\r\nSelect [Entity Type], [Owner name], [Object Name]\r\nfrom\r\n       (\r\nSELECT replace(SUBSTRING(v.name, 5, 31),'cns','constraint')  AS [entity type]\r\n    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS [owner name]\r\n    ,DB_NAME()+'.'+Object_Schema_name(o.object_ID)+'.'+o.name as [Object Name] \r\nFROM sys.objects o\r\nLEFT OUTER JOIN master.dbo.spt_values v--to get the type of object\r\n            ON o.type = SUBSTRING(v.name, 1, 2) COLLATE database_default\r\n               AND v.type = 'O9T'\r\nUNION \r\nSELECT 'Type' \r\n    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId'))\r\n    ,DB_NAME()+'.'+Schema_name(schema_ID)+'.'+name \r\n FROM sys.types  \r\nUNION\r\nSELECT 'XML Schema Collection'  \r\n    ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id))\r\n    ,DB_NAME()+'.'+Schema_name(xsc.schema_ID)+'.'+xsc.name \r\n       FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s\r\n    ON s.schema_id = xsc.schema_id\r\n    )f\r\nwhere [owner Name] not like 'sys'   \r\n<\/pre>\n
What’s been recently modified then?<\/h3>\n
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!<\/p>\n
SELECT  [Qualified_Name], Object_Type, CONVERT(CHAR(17), Created, 113),\r\n        CONVERT(CHAR(17), Last_Modified, 113)\r\nFROM    (SELECT --firstly, we'll search the names of the basic objects\r\n                DB_NAME()+'.'+Object_Schema_name(s.[object_ID])\r\n                    +'.'+COALESCE(p.name+'.', '')+s.name\r\n                AS [Qualified_Name],\r\n                REPLACE(SUBSTRING(v.name, 5, 31), 'cns', 'constraint')+' name'\r\n                AS Object_Type, s.create_date AS 'Created',\r\n                s.modify_date AS 'Last_Modified'\r\n         FROM   sys.objects S --to get the objects\r\n                LEFT OUTER JOIN master.dbo.spt_values v --to get the type of object\r\n                  ON s.type=SUBSTRING(v.name, 1, 2) COLLATE database_default \r\n                  AND v.type='O9T' \r\n                LEFT OUTER JOIN sys.objects p --to get any parent object\r\n                  ON s.parent_Object_ID=p.[object_ID]\r\n         WHERE  Object_Schema_name(s.object_ID) NOT LIKE 'sys%'\r\n         UNION ALL --now search the XML schema collection names\r\n         SELECT DB_NAME()+'.'+name, 'XML Schema Collection name',\r\n                create_date AS 'created', modify_date AS 'Last Modified'\r\n         FROM   sys.xml_schema_collections\r\n         UNION ALL\r\n         SELECT DB_NAME()+'.'+name, LOWER(type_desc)  COLLATE database_default,\r\n                create_date AS 'created', modify_date AS 'Last Modified'\r\n         FROM   sys.triggers\r\n         WHERE  parent_class=0--only DDL triggers\r\n         UNION ALL --names of CLR assemblies\r\n         SELECT DB_NAME()+'.'+name, 'CLR Assembly', create_date AS 'created',\r\n                modify_date AS 'Last Modified'\r\n         FROM   sys.assemblies\r\n         UNION ALL --almost done. We do the agent jobs too here\r\n         SELECT DISTINCT\r\n                'Agent'+'.'+DB_NAME()+'.'+[name]  COLLATE database_default,\r\n                'Agent Job', date_created, date_modified\r\n         FROM   MSDB.dbo.sysJobs Job \r\n           INNER JOIN MSDB.dbo.sysJobSteps Step ON Job.Job_Id=Step.Job_Id\r\n         WHERE  Database_name LIKE DB_NAME() COLLATE database_default) objects\r\nORDER BY Last_Modified DESC\r\n<\/pre>\n
Searching all your Databases<\/h2>\n
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 called sys.Databases<\/code>. You can then execute the code against all databases, collecting the result into a single table. Here is an example:<\/p>\n
DECLARE @ii INT, --loop counter         @iiMax INT, --loop counter upper limit\r\n       @CurrentDatabase VARCHAR(255), --variable holding name of current database\r\n       @command NVARCHAR(2000)--the dynamic command\r\n \r\nDECLARE @whatWeSearch TABLE --the table of all the databases we search\r\n  (Database_ID INT IDENTITY(1, 1),\r\n   DatabaseName VARCHAR(255)\r\n  )\r\nDECLARE @Result TABLE --the result\r\n  ([Tables Without Primary Keys] VARCHAR(255)\r\n  )\r\nINSERT INTO @whatWeSearch (DatabaseName) \r\n SELECT name FROM sys.Databases \r\n    WHERE name NOT IN ('Master', 'TempDB', 'Model', 'MSDB')\r\n--get all the databases we want to search\r\nSELECT @ii=MIN(Database_ID), @iiMax=MAX(Database_ID) FROM @whatWeSearch\r\n--and do them all one after another\r\nWHILE @ii<=@iiMax\r\n       BEGIN\r\n       SELECT @CurrentDatabase=QUOTENAME(DatabaseName) \r\n          FROM @whatWeSearch WHERE Database_ID=@ii\r\n       SET @Command=N'Use '+@CurrentDatabase+'\r\n       Select DB_NAME()+''.''+Object_Schema_name(t.object_ID)+''.''\r\n                                +t.name  as [tables without primary keys]\r\n       FROM sys.tables t\r\n       WHERE OBJECTPROPERTY(object_id,''TableHasPrimaryKey'') = 0\r\n       ORDER BY [tables without primary keys]'\r\n       INSERT INTO @Result ([Tables Without Primary Keys])\r\n              EXEC sp_executesql @Command\r\n       SELECT @ii=@ii+1 --and on to the next database\r\n       END\r\nSELECT [Tables Without Primary Keys] FROM @Result \r\n<\/pre>\n
Interrogating Object Dependencies<\/h2>\n
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.<\/p>\n
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.<\/p>\n
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 using sys.sp_refreshsqlmodule<\/code>, e.g.<\/p>\n
 sys.sp_refreshsqlmodule 'dbo.ufnGetContactInformation'<\/code><\/p>\n
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.<\/code><\/p>\n
--list all the dependencies in the database. Normally you'll have a WHERE clausee\r\n--to pick just the object you want.\r\n \r\nSELECT\r\nObject_Schema_name(object_id)+'.'+COALESCE(OBJECT_NAME(object_id), 'unknown')&NBSP;>\r\n  +COALESCE('.'+ COL_NAME(object_id, column_id), '') AS [referencer],\r\n  Object_Schema_name(referenced_major_id)+'.'+OBJECT_NAME(referenced_major_id)\r\n  +COALESCE('.'+COL_NAME(referenced_major_id, referenced_minor_id), '') AS [Referenced]\r\nFROM\r\n  sys.sql_dependencies\r\nWHERE\r\n  class IN (0, 1) --AND referenced_major_id = OBJECT_ID('HumanResources.Employee')\r\nORDER BY\r\n  COALESCE(OBJECT_NAME(object_id), 'x'),\r\n  COALESCE(COL_NAME(object_id, column_id), 'a') \r\n<\/pre>\n
\"962-Dependencies.jpg\"<\/p>\n
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.<\/p>\n
ALTER FUNCTION DependencyChainOf (@Object_Name VARCHAR(200))\r\n\/**\r\n summary:   >\r\n            The DependencyChainOf function takes as a parameter either a table\r\n            view, function or procedure name or a column name. It works best \r\n            with the full object name of schema.object(.column). returns a \r\n            table that gives the dependency chain with both forward and \r\n            backward links so that you can see what objects are likely to be\r\n            affected by the changes you make, and what objects your object\r\n            is referencing..\r\n Revisions:\r\n          - version: 1\r\n               Modification: Created Table-balued function\r\n               Author: Phil Factor\r\n               Date:  01\/03\/2010           \r\n          - version: 2\r\n               Modification: added catch for mutual dependency\r\n               Author: Phil Factor\r\n               Date:  02\/03\/2010   \r\nexample:\r\n       - code:\r\n              Select distinct * from DependencyChainOf('VEmployee') \r\n                      order by The_level,TheName\r\n       - code:\r\n              EXEC sys.sp_refreshsqlmodule 'MyProc1'\r\n              Select distinct * from DependencyChainOf('MyTable') \r\n                     order by y y y y y y The_level,TheName  \r\n  **\/\r\nRETURNS  @Referenced TABLE\r\n  (\r\n   TheName VARCHAR(200), The_Object_ID BIGINT, Column_ID INT,\r\n   Class INT, The_Level INT\r\n  )\r\n AS\r\n BEGIN \r\n--identify the object or  column\r\n--get the referencing entity\r\nINSERT INTO\r\n  @referenced (The_Object_ID, Column_ID, Class, The_Level)\r\n  SELECT TOP 1\r\n    object_ID, Column_ID, class, 1\r\n  FROM\r\n    (SELECT\r\n       Object_Schema_name(object_id)+'.'+COALESCE(OBJECT_NAME(object_id), 'unknown')\r\n          +COALESCE('.'+COL_NAME(object_id, column_id), '') AS [name], d.object_ID,\r\n       d.column_ID, class\r\n     FROM sys.sql_dependencies d\r\n    ) names\r\n  WHERE\r\n    CHARINDEX(REVERSE(@Object_Name), REVERSE(names.name))=1 \r\n              OR OBJECT_NAME([Object_ID])=@Object_Name\r\nIF NOT EXISTS ( SELECT 1 FROM @referenced ) \r\n  INSERT INTO\r\n    @referenced   (The_Object_ID, Column_ID, Class, The_Level)\r\n    SELECT TOP 1 object_ID, Column_ID, class, 1\r\n    FROM\r\n      (SELECT\r\n        Object_Schema_name(referenced_major_id)+'.'+OBJECT_NAME(referenced_major_id)\r\n              +COALESCE('.'+COL_NAME(referenced_major_id, referenced_minor_id), '') AS [name],\r\n        d.Referenced_Major_ID AS [object_ID],\r\n        d.Referenced_Minor_ID AS [column_ID], class\r\n       FROM\r\n        sys.sql_dependencies d\r\n        ) names\r\n    WHERE\r\n      CHARINDEX(REVERSE(@Object_Name), REVERSE(names.name))=1\r\n      OR OBJECT_NAME([Object_ID])=@Object_Name\r\nDECLARE  @Currentlevel INT, @RowCount INT\r\nSELECT  @Currentlevel=1, @Rowcount=1\r\nWHILE @Rowcount>0  AND @currentLevel<50--guard against mutual dependency\r\n  BEGIN\r\n    INSERT INTO @referenced (The_Object_ID, Column_ID, Class, The_Level)\r\n      SELECT Referenced_Major_ID, Referenced_Minor_ID, d.class, The_Level+1\r\n      FROM @referenced r\r\n        INNER JOIN sys.sql_dependencies d\r\n          ON The_Object_ID=object_ID\r\n             --AND r.column_ID=d.Column_ID\r\n             AND r.class=d.Class\r\n             AND @Currentlevel=The_Level\r\n    SELECT @rowcount=@@Rowcount, @CurrentLevel=@CurrentLevel+1\r\n  END\r\n  \r\nSELECT @Currentlevel=1, @Rowcount=1\r\nWHILE @Rowcount>0 AND @currentLevel>-50--guard against mutual dependency\r\n  BEGIN\r\n    INSERT INTO @referenced (The_Object_ID, Column_ID, Class, The_Level)\r\n      SELECT Object_ID, d.column_ID, d.class, The_Level-1\r\n      FROM\r\n        @referenced r\r\n        INNER JOIN sys.sql_dependencies d\r\n          ON The_Object_ID=Referenced_Major_ID\r\n             --AND r.column_ID=d.Referenced_Major_ID\r\n             AND r.class=d.Class\r\n             AND @Currentlevel=The_Level\r\n    SELECT\r\n      @rowcount=@@Rowcount, @CurrentLevel=@CurrentLevel-1\r\n  END\r\nUPDATE @Referenced SET TheName=\r\n    DB_NAME()+'.'+Object_Schema_name(The_object_ID)+'.'+OBJECT_NAME(The_object_ID)\r\n              +COALESCE('.'+COL_NAME(The_object_ID, column_id), '')\r\n  RETURN\r\n END   \r\n<\/pre>\n
\"962-DependencyChain3.jpg\"<\/p>\n
It’s worth noting that in SQL Server 2008, you would use the sys.sql_expression_dependencies<\/code> table, which has a much improved way of working out dependencies. There is a very full discussion, with example code, here at Reporting SQL Dependencies<\/a>. <\/p>\n
Summary<\/h2>\n
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.<\/p>\n
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.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4168,4150,4151,4252],"coauthors":[6813],"class_list":["post-821","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/821","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=821"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/821\/revisions"}],"predecessor-version":[{"id":92552,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/821\/revisions\/92552"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=821"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=821"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=821"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=821"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}