SQL 如何识别表是否有标识列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2871701/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to identify whether the table has identity column
提问by Shiny
I want to find out whether the table has an identity column or not. Table is unknown to me. I have not done the structure of the table. Using Query?
我想知道该表是否有标识列。我不知道表。表的结构我没做过。使用查询？
I am using Sql Server Compact Edition.
我正在使用 Sql Server 精简版。
回答by Pranay Rana
This query returns a table's identity column name:
此查询返回表的标识列名称：
CREATE PROCEDURE dbo.usp_GetIdentity
@schemaname nvarchar(128) = 'dbo'
,@tablename nvarchar(128)
AS
BEGIN
SELECT OBJECT_NAME(OBJECT_ID) AS TABLENAME,
NAME AS COLUMNNAME,
SEED_VALUE,
INCREMENT_VALUE,
LAST_VALUE,
IS_NOT_FOR_REPLICATION
FROM SYS.IDENTITY_COLUMNS
WHERE OBJECT_NAME(OBJECT_ID) = @tablename
AND OBJECT_SCHEMA_NAME(object_id) = @schemaname
END
Then form the code side.
然后形成代码端。
Call this stored procedure using the datareader role, then check datareader.hasrows(). If the condition value is true (1), then the table has identity column if set. If not then it doesn't have an identity column.
使用 datareader 角色调用此存储过程，然后检查datareader.hasrows(). 如果条件值为真 ( 1)，则表具有标识列（如果已设置）。如果不是，则它没有标识列。
回答by Wahid Bitar
I know it's long time ago but i found this helpful
我知道这是很久以前的事了，但我发现这很有帮助
try this :
尝试这个 ：
IF EXISTS (SELECT * from syscolumns where id = Object_ID(@TABLE_NAME) and colstat & 1 = 1)
BEGIN
-- Do your things
END
回答by h3n
IF (OBJECTPROPERTY(OBJECT_ID('TABLE_NAME'), 'TableHasIdentity') = 1)
ObjectPropertyis available starting sql server 2008 Reference:
OBJECTPROPERTY
ObjectProperty可启动sql server 2008 参考：
OBJECTPROPERTY
回答by Bhavneet
Any of the below queries can be used to check if an Identity Column is present in the table
以下任何查询均可用于检查表中是否存在标识列
1)
1)
SELECT *
FROM sys.identity_columns
WHERE OBJECT_NAME(object_id) = 'TableName'
2)
2)
SELECT *
FROM sys.identity_columns
WHERE object_id = (
SELECT id
FROM sysobjects
WHERE name = 'TableName'
)
回答by johnmcp
I would just like to add this option as well as I think it is the simplest
我只想添加这个选项，因为我认为它是最简单的
SELECT COLUMNPROPERTY(OBJECT_ID('TableName'),'ColumnName','isidentity')
回答by Kyle Rozendo
One way to do this would be to make use of the stored procedure sp_help. I.e:
一种方法是使用存储过程sp_help。IE：
sp_help MyTable
This will return a DataSet that has all the information you would need on the table. There is a specific Table that has information on identities.
这将返回一个数据集，其中包含您在表中需要的所有信息。有一个特定的表，其中包含有关身份的信息。
I.e:
IE：
If it does not contain an identity field, the Identity column will say: "No identity column defined".
如果它不包含标识字段，标识列将显示：“未定义标识列”。
回答by Patrick
@Pranay: he said Compact Edition. Stored procedures aren't supported, and there is no sys.anything.
@Pranay：他说的是精简版。不支持存储过程，并且没有 sys.anything。
This is the call:
这是电话：
SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE AUTOINC_INCREMENT IS NOT NULL AND TABLE_NAME='this_table'
SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE AUTOINC_INCREMENT IS NOT NULL AND TABLE_NAME='this_table'
It will return either 1 (true) or 0 (false).
它将返回 1（真）或 0（假）。
回答by Aravind Goud
This the query that get u all the tableNames, columnnames of the table, and is_identity or not in the selected database
这是获取所选数据库中所有表名、表列名和 is_identity 的查询
SELECT
sys.columns.name
, sys.tables.name
, is_identity
FROM sys.columns
INNER JOIN sys.tables ON sys.tables.object_id = sys.columns.object_id
AND sys.columns.is_identity = 1
回答by Sayed Muhammad Idrees
you can get the 1 or 0 BooleanForm if the current table has identity Columns by using this
如果当前表具有标识列，则可以使用此获取 1 或 0布尔形式
SELECT Count(Column_ID) FROM sys.identity_columns WHERE OBJECT_NAME(object_id) = 'tableName'
回答by user3415785
CREATE FUNCTION dbo.fnTableHasIdentity(@Tbl sysname)
RETURNS TINYINT
BEGIN
RETURN OBJECTPROPERTY(OBJECT_ID(@Tbl), 'TableHasIdentity')
END
--As simple as that!
- 就如此容易！
