如何识别该表是否有标识列

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精简版。

8 个解决方案

#1

This is the query which return identity column name;

这是返回标识列名称的查询;

create procedure GetIdentity 
@tablename varchar(50)
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
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()。如果条件值为true(1)，那么该表就具有标识列，如果没有设置，则它没有标识列。

#2

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

#3

Any of the below queries can be used to check if an Identity Column is present in the table

下面的任何查询都可以用来检查表中是否存在标识列

SELECT *
FROM sys.identity_columns
WHERE OBJECT_NAME(object_id) = 'TableName'

SELECT *
FROM sys.identity_columns
WHERE object_id = (
        SELECT id
        FROM sysobjects
        WHERE name = 'TableName'
    )

#4

I would just like to add this option as well as I think it is the simplest

我只想添加这个选项，我认为它是最简单的

SELECT COLUMNPROPERTY(OBJECT_ID('TableName'),'ColumnName','isidentity')

#5

IF (OBJECTPROPERTY(OBJECT_ID('TABLE_NAME'), 'TableHasIdentity') = 1)

ObjectProperty is available starting sql server 2008 Reference: OBJECTPROPERTY

ObjectProperty可以启动sql server 2008 Reference: ObjectProperty

#6

One way to do this would be to make use of the stored procedure sp_help. I.e:

一种方法是使用存储过程sp_help。即:

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:

即:

If it does not contain an identity field, the Identity column will say: "No identity column defined".

如果它不包含标识字段，标识列将会说:“没有定义标识列”。

#7

@Pranay: he said Compact Edition. Stored procedures aren't supported, and there is no sys.anything.

他说的是精简版。存储过程不受支持，也没有sys.anything。

This is the call:

这是电话:

SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE AUTOINC_INCREMENT IS NOT NULL AND TABLE_NAME='this_table'

从INFORMATION_SCHEMA SELECT Count(*)。AUTOINC_INCREMENT不为空的列和TABLE_NAME='this_table'

It will return either 1 (true) or 0 (false).

它将返回1 (true)或0 (false)。

#8

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

#1