Listing ON DELETE CASCADE Constraints in MS SQL Server

To check which tables have foreign key constraints with referential actions on other tables, use the sys.foreign_keys system view like this:

DECLARE @t NVARCHAR(128)
SET @t = 'My_Table_Name'

SELECT p.name, fk.delete_referential_action_desc, t.name
FROM sys.foreign_keys fk
INNER JOIN sys.objects t ON fk.parent_object_id = t.object_id
INNER JOIN sys.objects p ON fk.referenced_object_id = p.object_id
WHERE p.name = @t
ORDER BY 1, 3

If you want not only master-detail, but also grandparent-parent-child relations, you need a join over 3 tables and 2 foreign keys:

SELECT DISTINCT p.name, fk.delete_referential_action_desc, t.name, 
   fk2.delete_referential_action_desc, tt.name
FROM sys.foreign_keys fk
INNER JOIN sys.objects t ON fk.parent_object_id = t.object_id
INNER JOIN sys.objects p ON fk.referenced_object_id = p.object_id
INNER JOIN sys.foreign_keys fk2 ON fk2.referenced_object_id = t.object_id
INNER JOIN sys.objects tt ON fk2.parent_object_id = tt.object_id
WHERE p.name = @t
AND t.object_id <> p.object_id
ORDER BY 1, 3, 5

The filter on t.object_id <> p.object_id exludes circular relations in the first level which show up in the second level anyway.

Bad (MS SQL Server) habits to kick

Aaron Bertrand of SQLblog.com is writing a great series called Bad habits to kick, and two of his articles cover issues similar to some of my more recent posts:

Choosing the wrong data type deals with all SQL Server data types, where I only compared NVARCHAR vs VARCHAR. (Although I have to admit, I guess I won’t kick my habit of declaring INT integers even if the values never exceed 1-byte or 2-byte values 😉 )

His article on Inconsistent naming conventions reads like a confirmation of some articles of my series on Data Model Checks, where I described how to check naming conventions for tables and other objects, columns, and constraints and indexes.

Remember that consistency in naming and typing improves efficiency in programming. Once you decided for some kind of conventions, your need to look up column names and data types in the table definitions will reduce as it will become “obvious” for the programmers which column name uses which data type, and how a table or stored procedure is named exactly.

Checking Table Relations in MS SQL Server

To find isolated tables (that is, tables without foreign key constraints referencing to or from another table) in your data model, use this query:

SELECT  name
FROM    sys.objects t
LEFT OUTER JOIN sys.foreign_key_columns AS FKC1 
    ON t.object_id = FKC1.parent_object_id
LEFT OUTER JOIN sys.foreign_key_columns AS FKC2 
    ON t.object_id = FKC2.referenced_object_id
WHERE (t.type = 'U')
GROUP BY t.name
HAVING (COUNT(FKC1.constraint_object_id) = 0)
AND    (COUNT(FKC2.constraint_object_id) = 0)

If you have a naming convention for foreign key columns, such as FooID or Bar_ID, you can check this requirement:

SELECT Detail.name, DCol1.name AS Column1, Master.name
FROM sys.objects AS Detail
INNER JOIN sys.columns AS DCol1 ON Detail.object_id = DCol1.object_id
INNER JOIN sys.foreign_key_columns AS FKC1
    ON DCol1.column_id = FKC1.parent_column_id
    AND DCol1.object_id = FKC1.parent_object_id
INNER JOIN sys.objects AS Master ON FKC1.referenced_object_id = Master.object_id
WHERE DCol1.name NOT LIKE N'%[_]ID'
AND   DCol1.name NOT LIKE N'%[_]ID[_]%'

To check the opposite, i.e. a column with final “ID” is not part of a foreign key:

SELECT Detail.name, DCol1.name
FROM sys.objects AS Detail
INNER JOIN sys.columns AS DCol1 ON Detail.object_id = DCol1.object_id
LEFT OUTER JOIN sys.foreign_key_columns AS FKC1 
    ON  DCol1.column_id = FKC1.parent_column_id
    AND DCol1.object_id = FKC1.parent_object_id
WHERE (DCol1.name LIKE N'%[_]ID' or DCol1.name LIKE N'%[_]ID[_]%')
AND   (FKC1.constraint_object_id IS NULL)
AND   (Detail.type = 'U')

To check the requirement, that the foreign key of a column with a certain name must always reference a certain table:

SELECT Detail.name, Master.name
FROM sys.objects AS Detail
INNER JOIN sys.columns AS DCol1 ON Detail.object_id = DCol1.object_id
INNER JOIN sys.types on DCol1.system_type_id = sys.types.system_type_id
LEFT OUTER JOIN	sys.foreign_key_columns AS FKC1
    INNER JOIN	sys.objects AS Master 
        ON FKC1.referenced_object_id = Master.object_id
    ON DCol1.column_id = FKC1.parent_column_id
    AND DCol1.object_id = FKC1.parent_object_id
WHERE Detail.schema_id =1
AND   DCol1.name = 'STATUS'
AND   ISNULL(Master.Name, '') <> 'StatusValues'

And finally, to check whether all foreign key relations can make use of an index, use:

SELECT t.name, c.name, r.name, ic.index_column_id
FROM sys.objects t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.foreign_key_columns fkc 
    ON fkc.parent_object_id = c.object_id
    AND fkc.parent_column_id = c.column_id
INNER JOIN sys.objects fk ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.objects r ON fkc.referenced_object_id = r.object_id
LEFT OUTER JOIN sys.index_columns ic
        INNER JOIN sys.indexes i 
            ON  ic.object_id = i.object_id AND ic.index_id = i.index_id
    ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT OUTER JOIN sys.index_columns ic1
    ON  c.object_id = ic1.object_id 
    AND c.column_id = ic1.column_id 
    AND ic1.index_column_id = 1
WHERE t.schema_id = 1
AND   ISNULL(ic.index_column_id, -1) != 1
AND   ic1.index_column_id IS NULL

Finding Table Names not referenced by TSQL Code

A question on Stack Overflow titled TSQL script to find tables not being used by stored procedures, views, functions, etc? reminded me of a recent post on searching the TSQL source code catalog view of MS SQL Server.

This query would list all occurrences of all table names in T-SQL code, regardless whether the table name really refers to that table, or is just a substring of whatever identifier:

select t.name, sys.objects.name foundin, sys.objects.type_desc
from sys.objects t
left outer join
	sys.sql_modules
	inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
on sys.sql_modules.definition like '%' + t.name + '%'
where t.type = 'U'
order by t.name, type_desc, foundin

To list unreferenced table names, we add an IS NULL condition on the modules/objects:

select t.name, sys.objects.name foundin, sys.objects.type_desc
from sys.objects t
left outer join
	sys.sql_modules
	inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
on sys.sql_modules.definition like '%' + t.name + '%'
where t.type = 'U'
and sys.objects.name is null
order by t.name, type_desc, foundin

Doing the same check on views is a bit trickier, since the view definition contains the view name itself in the code. Therefore the OUTER JOIN condition needs to be adjusted to exclude the view’s own source code from the match:

select t.name, sys.objects.name foundin, sys.objects.type_desc
from sys.objects t
left outer join
	sys.sql_modules
	inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
on sys.sql_modules.definition like '%' + t.name + '%'
	and sys.objects.name != t.name
where t.type = 'V'
order by t.name, type_desc, foundin

Checking Naming Conventions of MSSQL Constraints and Indexes

If you create constraints and indexes in Management Studio, they are automatically named based on their type and the name of the table they belong to. If you create them manually, or overwrite their default names, naming may end up inconsistent.

Here are two simple queries to check for naming conventions.

Check that the constraint names match ‘constraint type’ + ‘table name’:

SELECT p.name, 
    CASE c.type
        WHEN 'F' THEN 'FK'
        WHEN 'D' THEN 'DF'
        WHEN 'UQ' THEN 'UK'
        WHEN 'C' THEN 'CK'
        ELSE c.type 
    END + ' ' + c.name
FROM sys.objects p
INNER JOIN sys.objects c ON p.object_id = c.parent_object_id
WHERE p.type = 'U'
AND c.name NOT LIKE
    CASE c.type
        WHEN 'F' THEN 'FK'
        WHEN 'D' THEN 'DF'
        WHEN 'UQ' THEN 'UK'
        WHEN 'C' THEN 'CK'
        ELSE c.type 
    END + '[_]' + p.name + '%'
ORDER BY 1, 2

Check that the index names match ‘index type’ + ‘table name’:

SELECT p.name, i.name
FROM   sys.objects p
INNER JOIN sys.indexes i ON p.object_id = i.object_id
WHERE p.type = 'U'
AND i.name NOT LIKE
    CASE
        WHEN i.is_primary_key = 1 THEN 'PK'
        WHEN i.is_unique = 1 THEN 'UK'
        ELSE 'IX'
    END + '[_]' + p.name + '%'
ORDER BY p.name, i.name

Checking Naming Conventions of MSSQL Database Columns

Conventions for columns inside a database project can target:

• Naming conventions for columns
• Required occurrences
• Data types
• Required foreign key constraints

and others.

So for example, if there is a rule for your database that every table needs to have a column named ID, this requirement can be checked with this query:

SELECT t.name
FROM   sys.objects AS t
LEFT OUTER JOIN sys.columns AS c ON t.object_id = c.object_id
    AND c.name = 'ID'
WHERE  (t.type = 'U')
AND    (c.name IS NULL)
ORDER BY t.name

Of course, you can restrict these checks to tables that match your table naming conventions.

Let’s check the requirement that every table must have a primary key:

SELECT sys.objects.name
FROM   sys.objects
INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
LEFT OUTER JOIN sys.index_columns ON sys.columns.column_id = sys.index_columns.column_id
    AND sys.columns.object_id = sys.index_columns.object_id
LEFT OUTER JOIN sys.indexes ON sys.objects.object_id = sys.indexes.object_id
    AND sys.indexes.is_primary_key = 1
WHERE  sys.objects.type = 'U'
AND    sys.indexes.object_id is null

Probably the primary key column always has to be an IDENTITY column:

SELECT sys.objects.name, sys.columns.name
FROM   sys.objects
INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
INNER JOIN sys.index_columns ON sys.columns.column_id = sys.index_columns.column_id
    AND sys.columns.object_id = sys.index_columns.object_id
INNER JOIN sys.indexes ON sys.objects.object_id = sys.indexes.object_id
    AND sys.indexes.index_id = sys.index_columns.index_id
WHERE  (sys.indexes.is_primary_key = 1)
AND    (sys.columns.is_identity = 0)

Checking Naming Conventions of MSSQL Database Objects

If you work on a project that has strict naming conventions on tables and views etc., it may be a good idea to check the database object names for that convention.

This can easily done by selecting the object names from sys.objects which do not match the naming convention:

SELECT Name
FROM   sys.objects
WHERE  schema_id = 1
AND    type IN ('U')
AND    name NOT LIKE '%Naming Convention for Tables%'
AND    name NOT LIKE 'aspnet%'
AND    name NOT IN ('sysdiagrams', ... other exclusions...)
ORDER BY name

In the WHERE condition, exclusions for the naming convention are added as NOT LIKE and NOT IN clauses. (e.g. sysdiagrams, aspnet tables etc.) If you use a schema other than dbo, you need to adjust the schema_id clause.

The naming check can by performed for any other type of database objects by adjusting the comparison of sys.objects.type. See here for the list of SQL Server types.

Occasionally you need to split these checks into several SELECT statements. If you require a project-specific prefix, and tables are to be named in plural, the first check (as above) would check for the prefix, the second check verifies the plural S:

SELECT Name
FROM   sys.objects
WHERE  schema_id = 1
AND    type IN ('U')
AND    name LIKE 'MyPrefix[_]%'
AND    name NOT LIKE '%S'
AND    name NOT IN (... list of known exclusions ...)
ORDER BY name

Searching MSSQL Source Code for Multiple Strings

When you search the SQL source code of MSSQL database objects, you can use the catalog view sys.sql_modules in a statement like this:

select      sys.objects.name , sys.objects.type_desc
from sys.sql_modules
inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_idwhere sys.sql_modules.definition like '%MySearchString%'
order by type_desc, sys.objects.name

When you need to search for more than one string, you either have to repeat the search with every string, or select a UNION of all the searches.

An easier way is to collection all the search strings in a single on-the-fly table, and cross join this table for the search expression:

select      ids.name searchfor, sys.objects.name foundin, sys.objects.type_desc
from sys.sql_modules
inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
cross join (
    select 'Search1' as name
    union all select 'Text2'
    union all ... etc
 ) ids
where sys.sql_modules.definition like '%' + ids.name + '%'
order by type_desc, foundin

An example for such a search would be that after renaming a number of views or stored procedures, you need to find all references to them throughout all your TSQL code.