How to identify whether the table has identity column

sql sql-server sql-server-ce
Shiny picture Shiny · May 20, 2010 · Viewed 49.2k times · Source

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.

Answer

Pranay Rana picture Pranay Rana · May 20, 2010

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.

Related questions

Why can't I use an alias in a DELETE statement?

In SQL Server Compact Edition in Visual Studio 2010 (maybe SQL Server and SQL in general, I don't know), this command works: DELETE FROM foods WHERE (name IN ('chickens', 'rabbits')) but this command produces an error of: Error near identifier f. …

Read More
SQL Server queries case sensitivity

I have this database: abcDEF ABCdef abcdef if I write: select * from MyTbl where A='ABCdef' how to get: ABCdef and how to get: abcDEF ABCdef abcdef Thanks in advance forgot to write - sqlCE

Read More
Determine if table exists in SQL Server CE?

I know this is similar to this question, but I'm using SQL Server CE 3.5 with a WinForms project in C#. How can I determine whether a table exists? I know the IF keyword is not supported, though EXISTS is. Does …

Read More