Problem
I often see people struggle with questions about finding all the tables that meet (or don’t meet) a certain criteria – no primary key, no clustered index, no identity column, more than <n> columns, more than <n> non-clustered indexes, and on and on.
A lot of people will search the web and find scripts that use old, deprecated backward compatibility views like sysobjects or – worse yet – the standard but incomplete INFORMATION_SCHEMA views.
Solution
There are many scripts I use over and over again, and since I continue to see slightly different questions with slightly different answers, but all along the same theme, I thought it would be useful to compile them in one place. (I’m going to leave out questions that can be answered quite simply by sys.tables alone, such as whether a table is involved in replication, is tracked by Change Data Capture, or was created with ANSI NULLS ON.)
Can you do most or all of these things in other ways (like PowerShell)? Of course. But I’m much more familiar with the catalog views and DMVs, so that’s where my advice will tend to lean. Most of these problem statements are self-explanatory, but I’ll add some commentary where I deem it will be helpful.
Note that these are just discovery scripts, meant only to list the tables that meet the given criteria – you will need to dig further to get more details about the objects and, more importantly, to fix any that you deem to be problems.
Also, most of these queries will run on SQL Server 2005 and up, with a few exceptions (filtered indexes, for example, did not exist until SQL Server 2008).
SQL Server Tables without a Primary Key
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS
(
SELECT 1 FROM sys.key_constraints AS k
WHERE k.[type] = N'PK'
AND k.parent_object_id = t.[object_id]
);
SQL Server Tables without a Unique Constraint
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS
(
SELECT 1 FROM sys.key_constraints AS k
WHERE k.[type] = N'UQ'
AND k,parent_object_id = t.[object_id]
);
SQL Server Tables without a Clustered Index (Heap)
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS
(
SELECT 1 FROM sys.indexes AS i
WHERE i.[object_id] = t.[object_id]
AND i.index_id = 1
);
SQL Server heaps with Forwarded Record Count > % of the Table
This can help identify heaps that need to be rebuilt to reclaim space (or simply heaps that maybe shouldn’t be heaps at all), with a filter for a minimum amount of rows in the table.
NOTE: sys.dm_db_index_physical_stats can be quite an expensive operation, so make sure you use these filters judiciously (or run this query against a restored backup or some kind of readable secondary).
DECLARE @percentage DECIMAL(5,2), @min_row_count INT;
SELECT @percentage = 10, @min_row_count = 1000;
;WITH x([table], [fwd_%]) AS
(
SELECT s.name + N'.' + t.name, CONVERT(DECIMAL(5,2), 100 * CONVERT(DECIMAL(18,2),
SUM(ps.forwarded_record_count)) / NULLIF(SUM(ps.record_count),0))
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),
t.[object_id], i.index_id, NULL, N'DETAILED') AS ps
WHERE i.index_id = 0
AND EXISTS
(
SELECT 1 FROM sys.partitions AS p
WHERE p.[object_id] = t.[object_id]
AND p.index_id = 0 -- heap
GROUP BY p.[object_id]
HAVING SUM(p.[rows]) >= @min_row_count
)
AND ps.record_count >= @min_row_count
AND ps.forwarded_record_count IS NOT NULL
GROUP BY s.name, t.name
)
SELECT [table], [fwd_%]
FROM x
WHERE [fwd_%] > @percentage
ORDER BY [fwd_%] DESC;
(I’m also assuming that you wouldn’t want to get per-partition details here, since partitioned heaps alone are a rarity, never mind the desire to rebuild individual partitions.)
SQL Server Tables without an Identity Column
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS
(
SELECT 1 FROM sys.identity_columns AS i
WHERE i.[object_id] = t.[object_id]
);
SQL Server Tables with at Least two Triggers
DECLARE @min_count INT;
SET @min_count = 2;
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.triggers AS tr
WHERE tr.parent_id = t.[object_id]
GROUP BY tr.parent_id
HAVING COUNT(*) >= @min_count
);
SQL Server Tables with at least one Disabled Trigger
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.triggers AS tr
WHERE tr.parent_id = t.[object_id]
AND tr.is_disabled = 1
);
SQL Server Tables with INSTEAD OF Triggers
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.triggers AS tr
WHERE tr.parent_id = t.[object_id]
AND tr.is_instead_of_trigger = 1
);
SQL Server Tables with More Than Twenty Columns
DECLARE @threshold INT;
SET @threshold = 20;
;WITH c([object_id], [column count]) AS
(
SELECT [object_id], COUNT(*)
FROM sys.columns
GROUP BY [object_id]
HAVING COUNT(*) > @threshold
)
SELECT [table] = s.name + N'.' + t.name,
c.[column count]
FROM c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
ORDER BY c.[column count] DESC;
SQL Server Tables that have at least one Column Name Matching N’%pattern%’
DECLARE @pattern NVARCHAR(128);
SET @pattern = N'%pattern%';
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.columns AS c
WHERE c.[object_id] = t.[object_id]
AND LOWER(c.name) LIKE LOWER(@pattern)
-- LOWER() due to potential case sensitivity
);
SQL Server Tables with at least one XML Column
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.columns AS c
WHERE c.[object_id] = t.[object_id]
AND c.system_type_id = 241 -- 241 = xml
);
SQL Server Tables with at least one LOB (MAX) Column
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.columns AS c
WHERE c.[object_id] = t.[object_id]
AND c.max_length = -1
AND c.system_type_id IN
(
165, -- varbinary
167, -- varchar
231 -- nvarchar
)
);
SQL Server Tables with at least one TEXT / NTEXT / IMAGE Column
These types are deprecated, and it is likely you will want to start thinking about moving them to VARCHAR(MAX) / NVARCHAR(MAX) / VARBINARY(MAX).
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.columns AS c
WHERE c.[object_id] = t.[object_id]
AND c.system_type_id IN
(
34, -- image
35, -- text
99 -- ntext
)
);
SQL Server Tables with at least one Alias Type Column
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.columns AS c
INNER JOIN sys.types AS typ
ON c.system_type_id = typ.system_type_id
AND c.user_type_id = typ.user_type_id
WHERE c.[object_id] = t.[object_id]
AND typ.is_user_defined = 1
-- AND type.name = N'something'
);
SQL Server Tables with Foreign Keys Referencing Other Tables
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = t.[object_id]
);
SQL Server Tables with Foreign Keys that Reference a Specific Table
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS pt -- "parent table"
ON fk.referenced_object_id = pt.[object_id]
INNER JOIN sys.schemas AS ps
ON pt.[schema_id] = ps.[schema_id]
WHERE fk.parent_object_id = t.[object_id]
AND ps.name = N'schema_name'
AND pt.name = N'table_name'
);
SQL Server Tables Referenced by Foreign Keys
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.foreign_keys AS fk
WHERE fk.referenced_object_id = t.[object_id]
);
SQL Server Tables with Foreign Keys that Cascade
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = t.[object_id]
AND (fk.delete_referential_action = 1
OR fk.update_referential_action = 1)
);
SQL Server Tables Referenced by Foreign Keys that Cascade
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.foreign_keys AS fk
WHERE fk.referenced_object_id = t.[object_id]
AND fk.delete_referential_action
+ fk.update_referential_action > 0
);
SQL Server Tables with Disabled Foreign Keys
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = t.[object_id]
AND fk.is_disabled = 1
);
SQL Server Tables with Untrusted Foreign Keys
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = t.[object_id]
AND fk.is_not_trusted = 1
);
SQL Server Tables with Self-Referencing Foreign Keys
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = t.[object_id]
AND fk.referenced_object_id = t.[object_id]
);
SQL Server Tables with Disabled Indexes
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.indexes AS i
WHERE i.[object_id] = t.[object_id]
AND i.is_disabled = 1
);
SQL Server Tables with Hypothetical Indexes
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.indexes AS i
WHERE i.[object_id] = t.[object_id]
AND i.is_hypothetical = 1
);
SQL Server Tables with Filtered Indexes
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.indexes AS i
WHERE i.[object_id] = t.[object_id]
AND i.has_filter = 1
);
SQL Server Tables with More Than Five Indexes
DECLARE @threshold INT;
SET @threshold = 5;
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.indexes AS i
WHERE i.[object_id] = t.[object_id]
GROUP BY i.[object_id]
HAVING COUNT(*) > @threshold
);
SQL Server Tables with More Than One Index with the Same Leading Key Column
These may very well be at least partially redundant indexes – we are also sure to check that the leading key column is defined in the same order, since there are use cases for one index ascending and another index descending. NOTE: We’re going to stick to heaps, clustered indexes and non-clustered indexes for now, ignoring XML indexes, as well as spatial, columnstore and hash indexes.
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic1
ON i.[object_id] = ic1.[object_id]
AND i.index_id = ic1.index_id
INNER JOIN sys.index_columns AS ic2
ON i.[object_id] = ic2.[object_id]
AND ic1.index_column_id = ic2.index_column_id
AND ic1.column_id = ic2.column_id
AND ic1.is_descending_key = ic2.is_descending_key
AND ic1.index_id <> ic2.index_id
WHERE i.[type] IN (0,1,2) -- heap, cix, ncix
AND ic1.index_column_id = 1
AND ic2.index_column_id = 1
AND i.[object_id] = t.[object_id]
GROUP BY i.[object_id]
HAVING COUNT(*) > 1
);
SQL Server Tables with Duplicate Indexes
This is a similar type of search, but is much more elaborate because all of the key columns have to be the same. While these are certainly redundant indexes (even if they may have different INCLUDE columns), I’d rather point you to Jason Strate’s blog post on this topic rather than try to re-invent the wheel here.
SQL Server Tables with a Default or Check Constraint
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.default_constraints AS d
WHERE d.parent_object_id = t.[object_id]
UNION ALL
SELECT 1 FROM sys.check_constraints AS c
WHERE c.parent_object_id = t.[object_id]
);
SQL Server Tables with a Default or Check Constraint Pointed at a UDF
You may be tempted to use scalar user-defined functions in check or default constraints, but I would recommend against it (for reasons why, see this post by Tibor Karazsi, this post by Tony Rogerson, and this post and this post by Alex Kuznetsov).
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.default_constraints AS dc
ON dc.[object_id] = d.referencing_id
INNER JOIN sys.objects AS udfs
ON d.referenced_id = udfs.[object_id]
WHERE dc.parent_object_id = t.[object_id]
AND udfs.[type] = 'FN'
UNION ALL
SELECT 1 FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.check_constraints AS c
ON c.[object_id] = d.referencing_id
INNER JOIN sys.objects AS udfs
ON d.referenced_id = udfs.[object_id]
WHERE c.parent_object_id = t.[object_id]
AND udfs.[type] = 'FN'
);
NOTE: The script above uses sys.sql_expression_dependencies, which was introduced in SQL Server 2008. In SQL Server 2005, you can use the backward compatibility view sys.sql_dependencies, but note that this view is deprecated, and shouldn’t be used in newer versions of SQL Server.
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.sql_dependencies AS d
INNER JOIN sys.default_constraints AS dc
ON dc.[object_id] = d.[object_id]
INNER JOIN sys.objects AS udfs
ON d.referenced_major_id = udfs.[object_id]
WHERE dc.parent_object_id = t.[object_id]
AND udfs.[type] = 'FN'
UNION ALL
SELECT 1 FROM sys.sql_dependencies AS d
INNER JOIN sys.check_constraints AS c
ON c.[object_id] = d.[object_id]
INNER JOIN sys.objects AS udfs
ON d.referenced_major_id = udfs.[object_id]
WHERE c.parent_object_id = t.[object_id]
AND udfs.[type] = 'FN'
);
SQL Server Tables with at least One Untrusted Check Constraint
This will be a subset of the previous query, but is much simpler because only check constraints using a function can become is_not_trusted.
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.check_constraints AS c
WHERE c.parent_object_id = t.[object_id]
AND c.is_not_trusted = 1
);
SQL Server Tables with at least One System-Named Constraint
These are those ugly names like UQ__TableName__DECAF6925905B5A7.
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t<br> INNER JOIN sys.schemas AS s<br> ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = t.[object_id]
AND fk.is_system_named = 1
UNION ALL
SELECT 1 FROM sys.key_constraints AS k
WHERE k.parent_object_id = t.[object_id]
AND k.is_system_named = 1
UNION ALL
SELECT 1 FROM sys.default_constraints AS d
WHERE d.parent_object_id = t.[object_id]
AND d.is_system_named = 1
UNION ALL
SELECT 1 FROM sys.check_constraints AS c
WHERE c.parent_object_id = t.[object_id]
AND c.is_system_named = 1
);
SQL Server Tables with More (or Less) Than X Rows
A lot of people will create a loop and perform a heavy SELECT COUNT(*) against every table. Or they’ll loop through and insert the results of sp_spaceused into a #temp table, then filter the results. It is much less intrusive to just check the catalog view sys.partitions:
DECLARE @threshold INT;
SET @threshold = 100000;
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.partitions AS p
WHERE p.[object_id] = t.[object_id]
AND p.index_id IN (0,1)
GROUP BY p.[object_id]
HAVING SUM(p.[rows]) > @threshold
);
All SQL Server Tables in a Schema
DECLARE @schema SYSNAME;
SET @schema = N'some_schema';
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = @schema;
SQL Server Tables Referenced Directly by at least one View
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.tables AS st
INNER JOIN sys.schemas AS ss
ON st.[schema_id] = ss.[schema_id]
CROSS APPLY sys.dm_sql_referencing_entities
(QUOTENAME(ss.name) + N'.' + QUOTENAME(st.name),
N'OBJECT') AS r
INNER JOIN sys.views AS v
ON v.[object_id] = r.referencing_id
INNER JOIN sys.schemas AS vs
ON v.[schema_id] = vs.[schema_id]
WHERE st.[object_id] = t.[object_id]
);
If you’re on SQL Server 2005, you’ll need to use the deprecated sys.sql_dependencies view:
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1
FROM sys.sql_dependencies AS d
INNER JOIN sys.tables AS st
ON st.[object_id] = d.referenced_major_id
INNER JOIN sys.views AS v
ON d.[object_id] = v.[object_id]
WHERE st.[object_id] = t.[object_id]
);
NOTE: These view queries only go one level deep – they’re not going to find views that reference other views that ultimately reference tables.
SQL Server Tables Referenced Directly by at least one Indexed View
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT * FROM sys.tables AS st
INNER JOIN sys.schemas AS ss
ON st.[schema_id] = ss.[schema_id]
CROSS APPLY sys.dm_sql_referencing_entities
(QUOTENAME(ss.name) + N'.' + QUOTENAME(st.name),
N'OBJECT') AS r
INNER JOIN sys.views AS v
ON v.[object_id] = r.referencing_id
INNER JOIN sys.schemas AS vs
ON v.[schema_id] = vs.[schema_id]
INNER JOIN sys.indexes AS i
ON v.[object_id] = i.[object_id]
WHERE i.index_id = 1
AND st.[object_id] = t.[object_id]
);
And again, for SQL Server 2005:
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1
FROM sys.sql_dependencies AS d
INNER JOIN sys.tables AS st
ON st.[object_id] = d.referenced_major_id
INNER JOIN sys.views AS v
ON d.[object_id] = v.[object_id]
INNER JOIN sys.indexes AS i
ON v.[object_id] = i.[object_id]
WHERE i.index_id = 1
AND st.[object_id] = t.[object_id]
);
SQL Server Tables Referenced Directly by at least one View that uses SELECT *
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.tables AS st
INNER JOIN sys.schemas AS ss
ON st.[schema_id] = ss.[schema_id]
CROSS APPLY sys.dm_sql_referencing_entities
(QUOTENAME(ss.name) + N'.' + QUOTENAME(st.name),
N'OBJECT') AS r1
INNER JOIN sys.views AS v
ON v.[object_id] = r1.referencing_id
INNER JOIN sys.schemas AS vs
ON v.[schema_id] = vs.[schema_id]
CROSS APPLY sys.dm_sql_referenced_entities
(QUOTENAME(vs.name) + N'.' + QUOTENAME(v.name),
N'OBJECT') AS r2
WHERE r2.is_select_all = 1
AND st.[object_id] = t.[object_id]
);
Once more, in 2005:
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT *
FROM sys.sql_dependencies AS d
INNER JOIN sys.tables AS st
ON st.[object_id] = d.referenced_major_id
INNER JOIN sys.views AS v
ON d.[object_id] = v.[object_id]
WHERE d.is_select_all = 1
AND st.[object_id] = t.[object_id]
);
SQL Server tables Referenced Directly by Schema-Bound Objects
SELECT [table] = s.name + N'.' + t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.tables AS st
INNER JOIN sys.schemas AS ss
ON st.[schema_id] = ss.[schema_id]
CROSS APPLY sys.dm_sql_referencing_entities
(QUOTENAME(ss.name) + N'.' + QUOTENAME(st.name),
N'OBJECT') AS r
INNER JOIN sys.objects AS o
ON o.[object_id] = r.referencing_id
INNER JOIN sys.sql_modules AS m
ON o.[object_id] = m.[object_id]
WHERE m.is_schema_bound = 1
AND st.[object_id] = t.[object_id]
);
SQL Server Tables Referenced by local Synonyms
It would be pretty hard to write a T-SQL script that would go out and find all of the instances of SQL Server that have a synonym that points to the local machine – especially since some of those might be servers that only have a linked server in one direction (never mind that they may be impossible to discover anyway). So here’s something that gets you close – it at least scans all of the local, online databases that have synonyms pointing to objects in the local database.
DECLARE @sql NVARCHAR(MAX), @exec NVARCHAR(MAX);
SELECT @sql = N'', @exec = QUOTENAME(DB_NAME()) + N'.sys.sp_executesql';
SELECT @sql = @sql + N'
UNION ALL SELECT [database] = N''' + REPLACE(db.name, '''', '''''')
+ ''', [synonym] = syn.name, points_to = syn.base_object_name'
+ N' FROM ' + QUOTENAME(db.name) + N'.sys.synonyms AS syn
WHERE PARSENAME(syn.base_object_name, 3) = DB_NAME()
AND COALESCE(PARSENAME(syn.base_object_name, 4), @srv) = @srv
AND EXISTS
(
SELECT 1 FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = PARSENAME(syn.base_object_name, 1)
AND s.name = PARSENAME(syn.base_object_name, 2)
)'
FROM sys.databases AS db WHERE [state] = 0;
SET @sql = STUFF(@sql, 1, 11, N'');
EXEC @exec @sql, N'@srv SYSNAME', @srv = @@SERVERNAME;
NOTE: This won’t correctly identify synonyms pointing at the local database if you have used additional loopback or local linked servers defined using something other than @@SERVERNAME.
Conclusion
I hope that has provided a good variety of metadata queries to help you identify sets of tables that meet some criteria. I certainly didn’t cover every potential scenario, but tried to touch enough areas to give you a head start on any areas I didn’t hit directly.
Next Steps
- Bookmark this page, in case you need quick access to these code samples in the future.
- Review the following tips and other resources:
