how to identify tables without column identity in sql server?

Question

how to identify tables without column identity in sql server?

Navigation

#1 by (3 votes)

2

I need to control the SQL bases (several tables) which must have Identity Identifiers in the 'Id' column that they all have. The closest thing I came to this:

SELECT
distinct TABLE_NAME,COLUMN_Name
FROM INFORMATION_SCHEMA.COLUMNS,
sys.Objects so
WHERE
TABLE_SCHEMA = 'dbo' 
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
and so.Type = 'U' And so.name = 'Id'
ORDER BY
TABLE_NAME

This query only gives me the tables and columns that Identity has, and since there are several bases of 180 tables each, it is cumbersome to look for them. Thanks in advance.

sql

asked by jkvalero 23.03.2018 в 16:29

source

1 answer

When to use the tag # in an input text? Delete the characters in two strings if the character matches the string

score 3 · Answer 1

The following query can help you know the name of the table and if it has a field of type IDENTITY . It is simply to make JOIN to the definition of tables and columns with respect to the key field object_id :

SELECT t.name, c.name
FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
WHERE c.is_identity = 1
ORDER BY t.name

To list with a single query all fields that are IDENTITY of all databases would be as follows:

DECLARE @sql NVARCHAR(max);

SET @sql = N'select cast(''master'' as sysname) as db_name, t.name collate Latin1_General_CI_AI Tabla, c.name collate Latin1_General_CI_AI Columna, c.is_identity from master.sys.tables t INNER JOIN master.sys.columns c ON t.object_id = c.object_id WHERE c.is_identity = 1';

SELECT @sql = @sql + N' union all select ' + quotename(name, '''') + ', t.name collate Latin1_General_CI_AI, c.name collate Latin1_General_CI_AI object_id, c.is_identity from ' + quotename(name) + N'.sys.tables t INNER JOIN ' + quotename(name) + '.sys.columns c ON t.object_id = c.object_id WHERE c.is_identity = 1'
FROM sys.databases
WHERE database_id > 1
    AND STATE = 0
    AND user_access = 0
    AND name NOT IN (
        'tempdb'
        ,'model'
        ,'msdb'
        )

--PRINT @sql
EXEC sp_executesql @sql;