THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Identifying Identities

So I saw this article the other day that talked about using identities in the database. One snippet of code in the article was supposed to find tables with identity columns. I saw two key issues with this code:

  1. The code referenced system tables. If at all possible, one should avoid system tables references in favor of the Information Schema views.
  2. The code didn't account for object ownership (schema). If two or more different users had an object with the same name (Jane.SomeTable, John.SomeTable, dbo.SomeTable), then the owner information needs to be specified when using functions like IDENT_CURRENT and OBJECT_ID and when joining the Information Schema Tables and Columns views.

So, being the geek that I am, I felt compelled to write this more complete version of that code that does in fact use the Information Schema views and handles object ownership:

SELECT T.TABLE_SCHEMA, T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE,
       IDENT_CURRENT(T.TABLE_SCHEMA + '.' + T.TABLE_NAME) [CURRENT_IDENTITY_VALUE]
FROM INFORMATION_SCHEMA.TABLES AS T (NOLOCK)
        INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C (NOLOCK)
        ON C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE'
 AND  COLUMNPROPERTY (OBJECT_ID(T.TABLE_SCHEMA + '.' + T.TABLE_NAME),
                      C.COLUMN_NAME, 'IsIdentity') = 1
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME, C.COLUMN_NAME

--Peter

Published Monday, June 26, 2006 12:59 PM by Peter W. DeBetta

Comments

No Comments
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement