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


By:   |   Updated: 2014-11-19   |   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 columns, more than 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


Last Updated: 2014-11-19


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, October 14, 2015 - 3:45:42 PM - RPSQL Back To Top

Many Thanks.


Monday, March 02, 2015 - 9:13:10 AM - Aaron Bertrand Back To Top

@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.


Monday, March 02, 2015 - 2:39:01 AM - fateswing Back To Top
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;
 
 
This query doesn't work !
 
The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement. This dynamic management function does not accept correlated parameters from CROSS APPLY and OUTER APPLY.
https://msdn.microsoft.com/en-us/library/ms188917.aspx

Sunday, December 21, 2014 - 7:44:56 AM - Hany Helmy Back To Top

Great article, very useful and informative, especially that part for the Physical Duplicate Indexes.

 

Thanx

Hany


Thursday, November 20, 2014 - 4:50:27 PM - Paul K Back To Top

Immense Aaron. Thank you.

One more for the pot. Tables holding GUIDs (hopefully not 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 EXISTS
  (
    SELECT 1 FROM sys.columns AS c
      WHERE c.[object_id] = t.[object_id]
      AND c.system_type_id = 36 -- uniqueidentifier
  );


Thursday, November 20, 2014 - 7:57:35 AM - Patrick Lambin Back To Top

As usually , Aaron Bertrand is providing a very good and useful tip well documented and easy to understand even for a beginner. For this tip , I send your an awesome cheer . Maybe a little reproach , even if I think he does not do because a lack of space : some examples of results could be nice , making easier to understand the results : some examples of the results given by your scripts.

Anyway , many thanks to share this tips and I think I will need time to test and to understand the results.


Thursday, November 20, 2014 - 5:59:39 AM - Mike Button Back To Top

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

thanks a lot for sharing!


Wednesday, November 19, 2014 - 2:32:32 PM - Sri Back To Top

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

Thanks Aaron.  The link answered the question well.


Wednesday, November 19, 2014 - 1:08:44 PM - Aaron Bertrand Back To Top

Hi DJJ,

Well, if I know I'm after tables, there's little reason to use sys.objects since it also contains a whole lot of things that aren't tables. I'd still prefer the join to sys.schemas over metadata functions like SCHEMA_NAME() - see this post for more info:

http://blogs.sqlsentry.com/aaronbertrand/bad-habits-metadata-helper-functions/


Wednesday, November 19, 2014 - 12:50:05 PM - DJJ Back To Top

Great list.  Any comment on using sys.tables / sys.schemas versus sys.objects?

Example:

-- Your code
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
  );

-- Another way
SELECT 'No Clustered Indexes' AS TypeQuery, DB_NAME() + '.' + SCHEMA_NAME(so.schema_id) + '.' + so.name AS TableName
FROM sys.objects so
LEFT JOIN sys.indexes si on SO.[object_id] = si.[object_id]
                            AND si.index_id = 1
WHERE so.type IN ('TT', 'U')
    AND si.[object_id] IS NULL;

 


Wednesday, November 19, 2014 - 11:25:16 AM - Aaron Bertrand Back To Top

Jack, thanks, fixed.


Wednesday, November 19, 2014 - 11:24:39 AM - Aaron Bertrand Back To Top

RJ yes, sorry, human here. You'll need to copy the join to sys.schemas (in just about every other query) that I somehow missed for that one:

   INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]

Wednesday, November 19, 2014 - 10:25:46 AM - Ted Back To Top

Great stuff Aaron, appreciate you sharing these TSQL queries.  Also, thanks for the links you included, I'll go and research those as well!


Wednesday, November 19, 2014 - 9:40:59 AM - RJ Book Back To Top

The code in the "SQL Server Tables with at least One System-Named Constraint" section does not parse, I receive the following result running on SQL2008

 

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "s.name" could not be bound. 


Wednesday, November 19, 2014 - 8:25:35 AM - Jack Corbett Back To Top

Aaron,

Your code for INSTEAD OF triggers is the same as your code for DISABLED triggers. You need to change the is_disabled = 1 to is_instead_of_trigger = 1.

Otherwise a great set of tips.


Wednesday, November 19, 2014 - 6:31:18 AM - Eman Back To Top

ooooooooooooooohh

 

itis tooooooooo useful for me,

Thanks alot



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools