logo标签列表

如何通过编程确定哪些SQL表具有标识列

sql-servert-sqlmetadataidentity-column
113

我想在SQL Server 2005中创建一个包含标识列以及它们对应的表格的列列表。

结果应该类似于:

TableName, ColumnName

14个回答
1

Use this :

DECLARE @Table_Name VARCHAR(100) 
DECLARE @Column_Name VARCHAR(100)
SET @Table_Name = ''
SET @Column_Name = ''

SELECT  RowNumber = ROW_NUMBER() OVER ( PARTITION BY T.[Name] ORDER BY T.[Name], C.column_id ) ,
    SCHEMA_NAME(T.schema_id) AS SchemaName ,
    T.[Name] AS Table_Name ,
    C.[Name] AS Field_Name ,
    sysType.name ,
    C.max_length ,
    C.is_nullable ,
    C.is_identity ,
    C.scale ,
    C.precision
FROM    Sys.Tables AS T
    LEFT JOIN Sys.Columns AS C ON ( T.[Object_Id] = C.[Object_Id] )
    LEFT JOIN sys.types AS sysType ON ( C.user_type_id = sysType.user_type_id )
WHERE   ( Type = 'U' )
    AND ( C.Name LIKE '%' + @Column_Name + '%' )
    AND ( T.Name LIKE '%' + @Table_Name + '%' )
ORDER BY T.[Name] ,
    C.column_id
1
这对我有用，使用 Sql Server 2008：

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    , t.name AS table_name
    , c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO
1
由于某些原因，SQL Server会将一些标识列保存在不同的表中。对我有效的代码如下：
select      TABLE_NAME tabla,COLUMN_NAME columna
from        INFORMATION_SCHEMA.COLUMNS
where       COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
union all
select      o.name tabla, c.name columna
from        sys.objects o 
inner join  sys.columns c on o.object_id = c.object_id
where       c.is_identity = 1
0

获取所有包含 Identity 的列。适用于 MSSQL 2017+ 的现代版本。锁定到特定数据库：

SELECT
   [COLUMN_NAME]
   , [TABLE_NAME]
   , [TABLE_CATALOG]
FROM
   [INFORMATION_SCHEMA].[COLUMNS]
WHERE
   COLUMNPROPERTY(OBJECT_ID(CONCAT_WS('.' ,[TABLE_CATALOG] ,[TABLE_SCHEMA] ,[TABLE_NAME])) ,[COLUMN_NAME] ,'IsIdentity') = 1
ORDER BY
   [TABLE_NAME]
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接