StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Try this: </p> <pre><code>SELECT pk_column_name = c.name, table_name = o.name FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.syscolumns c ON ic.object_id = c.id AND COL_NAME(ic.object_id, ic.column_id) = c.name INNER JOIN sys.sysobjects o ON o.id = c.id WHERE i.is_primary_key = 1 -- the column is part of a primary key AND c.status < 128 -- the column is NOT identity enabled AND o.xtype = 'U' -- the object is a user table AND c.xtype = 56 -- the column type is int </code></pre> <p>I am including columns that are of the type 'int'. If you want to include types like smallint as well, you can look up its xtype by doing a select on the sys.systypes DMV:</p> <pre><code>SELECT name, xtype FROM sys.systypes </code></pre> <p>Here is an alternative way of doing this using information schema views:</p> <pre><code>SELECT pk_column_name = c.COLUMN_NAME, table_name = c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k ON c.TABLE_NAME = k.TABLE_NAME AND c.TABLE_SCHEMA = k.TABLE_SCHEMA AND c.TABLE_CATALOG = k.TABLE_CATALOG AND c.COLUMN_NAME = k.COLUMN_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS const ON k.TABLE_NAME = const.TABLE_NAME AND k.CONSTRAINT_CATALOG = const.CONSTRAINT_CATALOG AND k.CONSTRAINT_SCHEMA = const.CONSTRAINT_SCHEMA AND k.CONSTRAINT_NAME = const.CONSTRAINT_NAME WHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') = 0 AND const.CONSTRAINT_TYPE = 'PRIMARY KEY' AND c.DATA_TYPE = 'int' </code></pre> <p>Hope this helps. Note that this only works for SQL Server 2005 and above. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload