As database administrators and developers, it's our job to know the in's and out's of the data in our charge. Whether we're designing entity relationships to support underlying business requirements, optimizing indexes to achieve the best response times, or writing a query to retrieve the right data in the most efficient manner, having an intimate knowledge of the database is key.
However, since few of us are responsible for just one database, it's sometimes difficult to gain, much less maintain, that level of familiarity with our data.
Fortunately for us, however, we have the metadata available to us. We can use SQL Server's system tables and views to seek out the information we need to help us do our jobs.
For example, I was recently asked by a client to begin considering what it would take to retrofit replication into an existing application. Now obviously it's best to know before the design phase ever begins that replication may be an option, but atlas hindsight is always 20-20 and circumstances change.
One of my first thoughts was that of identity columns. If identity columns are widespread throughout a database design, that must be addressed as replication is considered.
So, how many tables in my client's employ identity columns? Good question. I don't know off-hand. But fortunately the information is at my fingertips.
The following query can be used to list all tables in a database where an identity column is defined.
SELECT
t.TABLE_NAME
,c.COLUMN_NAME
,c.TABLE_CATALOG
,c.TABLE_SCHEMA
FROM
INFORMATION_SCHEMA.COLUMNS AS c JOIN
INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_NAME = c.TABLE_NAME
WHERE
COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME)
,c.COLUMN_NAME,'IsIdentity') = 1 AND
t.TABLE_TYPE = 'Base Table' AND
t.TABLE_NAME NOT LIKE 'dt%' AND
t.TABLE_NAME NOT LIKE 'MS%' AND
t.TABLE_NAME NOT LIKE 'syncobj_%'
Running the query in the AdventureWorksDW database produces the following results.
Cheers!
Joe
