Over 40 queries to find SQL Server tables with or without a property

By: Aaron Bertrand | Comments (18) | Related: More > Database Administration

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
  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_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:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Wednesday, October 14, 2015 - 3:45:42 PM - RPSQL	Back To Top (38896)
Many Thanks.

Monday, March 2, 2015 - 9:13:10 AM - Aaron Bertrand	Back To Top (36401)
@fateswing, sorry, that query should work in SQL Server 2012 and newer. I believe the modern versions of the documentation are out of date because that restriction has been lifted.

Thursday, November 20, 2014 - 5:59:39 AM - Mike Button	Back To Top (35349)
Aaron, all the queries we've ever wanted/needed for finding tables. Magic. Thank you !!

Wednesday, November 19, 2014 - 4:51:36 PM - Aschalew Haile	Back To Top (35345)
thanks a lot for sharing!

Wednesday, November 19, 2014 - 2:32:32 PM - Sri	Back To Top (35344)
Awesome. All scripts in one single place. Thanks so much Aaron for sharing 'em all!!!

Wednesday, November 19, 2014 - 2:22:00 PM - DJJ	Back To Top (35343)
Thanks Aaron. The link answered the question well.

Wednesday, November 19, 2014 - 11:25:16 AM - Aaron Bertrand	Back To Top (35340)
Jack, thanks, fixed.

Wednesday, November 19, 2014 - 10:25:46 AM - Ted	Back To Top (35338)
Great stuff Aaron, appreciate you sharing these TSQL queries. Also, thanks for the links you included, I'll go and research those as well!

Over 40 queries to find SQL Server tables with or without a certain property