SQL Describe - MS SQL equivalent?

G

generelz

Limp Gawd
Joined
May 12, 2005
Messages
395
I have run into a problem - I am in the process of writing a module that will ensure each table in our database schema has the correct primary key column(s) set. The first iteration I wrote does this under MySQL and now I am set to tackle MS SQL. However, I have run into a problem.

MySQL supports the "DESCRIBE" operator which when executed describes the table structure, giving a result set containing the following columns: "Field", "Type", "Null", "Key", "Default", and "Extra".

Ultimately I was able to use the results of this operator to determine if a column (Field) is defined as a primary key column by looking at the contents of the Key column (when Key = 'PRI'.).

Now, in MS SQL I have yet to devise a way to determine the primary key column(s) for a table. I know about the sysobjects table - and how to determine what the object is that defines the primary key - but I don't know how to get the columns it references. Here is the query I have come up with:

Code: 
SELECT * FROM sysobjects WHERE parent_obj = (SELECT id FROM sysobjects WHERE name = 'table_name') AND xtype='PK'

If there is another, more straightforward solution that involves relatively standard SQL, I would appreciate it if someone could point me off in that direction. For now I am browsing the MSDN library trying to find some hints - however my searches thus far have not turned up much!

Thanks in advance for any help or suggestions.
 
You'll not find a standard SQL way of finding the primary key for a table because metadata and catalog inforamtion isn't a part of the standard.

Further, the "primary key" of a table is a logical construct, and isn't necessarily known to the database. When I create a table, I might just create it and not even declare an index -- yet still logically know that one of the columns is a primary key. I might create an index on that column, but nothing about the index lets us know that it is a primary key. I might also use a PRIMARY KEY constraint, which does let the database know that the particular column set is the key.

Is it adequate for your application to find only columns that are marked with a PRIMARY KEY constraint?
 
mikeblas said:
You'll not find a standard SQL way of finding the primary key for a table because metadata and catalog inforamtion isn't a part of the standard.
Click to expand...

Agreed - I see that now. However I was under the impression that the DESCRIBE operation was an ANSI standard, which would provide a standard way for viewing the definition of a table...but I guess I am mistaken.

mikeblas said:
Further, the "primary key" of a table is a logical construct, and isn't necessarily known to the database. When I create a table, I might just create it and not even declare an index -- yet still logically know that one of the columns is a primary key. I might create an index on that column, but nothing about the index lets us know that it is a primary key. I might also use a PRIMARY KEY constraint, which does let the database know that the particular column set is the key.
Click to expand...

100% With you here. This really is the crux of our problem - the tables were created and we knew that a certain column/columns were the primary key - but did not tell the database hence the database is not yet able to optimize for the fact that we will frequently be querying things by that primary key. We wish to specify the PRIMARY KEY constraint for data integrity purposes as well as the performance improvement (implicitly created unique clustered index). I should have been more specific, which leads us to...

mikeblas said:
Is it adequate for your application to find only columns that are marked with a PRIMARY KEY constraint?
Click to expand...

Yes, this is what I meant to say - find the columns of a table that are included within a PRIMARY KEY constraint.
 
generelz said:
Agreed - I see that now. However I was under the impression that the DESCRIBE operation was an ANSI standard, which would provide a standard way for viewing the definition of a table...but I guess I am mistaken.
Click to expand...
The only DESCRIBE I know about in the ANSI standard is about dynamic SQL. Not to start an a r-war, but I don't really think the ANSI standard is relevant. Every vendor has so many extensions, and lots of shortage, too, that the standard really isn't of practical use.

generelz said:
Yes, this is what I meant to say - find the columns of a table that are included within a PRIMARY KEY constraint.
Click to expand...

This post is provided without warranty and confers no rights, and so on. You should test what I'm suggesting because I just whipped it up to try and help you out, and didn't test it thoroughly.

So, first, you can use sp_help. "sp_help mytable" will dump a description of the table, including any constraints, PRIMARY KEY or not; as well as other indexes (unique or not).

This query will look at the catalog views and find all the columns for the PK constraints on the table name you offer. If you remove the AND clause with the table name (the last term at the end of the statement), you can dump everything for all user tables.

Code: 
SELECT sys.sysobjects.name as TableName, sys.sysindexes.name AS IndexName, sys.syscolumns.name AS KeyColumn from sysobjects 
  JOIN sys.key_constraints ON parent_object_id = sys.sysobjects.id
  JOIN sys.sysindexes ON sys.sysindexes.id = sys.sysobjects.id
  JOIN sys.index_columns ON sys.index_columns.object_id = sys.sysindexes.id 
  JOIN sys.syscolumns ON sys.syscolumns.id = sys.sysindexes.id AND sys.index_columns.column_id = sys.syscolumns.colid
 WHERE sys.sysobjects.type = 'u'               -- 'U' means user table
   AND sys.key_constraints.type = 'PK'         -- we're looking for PK constraints
   AND sys.sysobjects.name = 'factory_process' -- the table we want to query
 
mikeblas said:
*snip*

This query will look at the catalog views and find all the columns for the PK constraints on the table name you offer. If you remove the AND clause with the table name (the last term at the end of the statement), you can dump everything for all user tables.

*snip*
Click to expand...

Thanks Mike, this helps a lot. I wasn't aware of all the information available so I had no idea how to structure the query - this definitely gets me 99% of the way there.

It appears this query is listing all indexed columns - rather than just columns that are included in an index created as a result of setting a primary key constraint. I'm working on decyphering the structure of the tables referenced in the query to see if I can modify it by adding a WHERE clause or another JOIN clause to limit to ONLY primary key constraint related columns. I know about the "xtype" column in the sysobjects view but I am not quite sure how to use that...yet...

Thanks again Mike. I really appreciate all your help. If you ever need any Java questions answered let me know :p
 
I'll see if I can fix that. These queries are a little confusing to write becuase "objectID" is overloaded -- in this query, there's the table objectID, the index objectID, and the constraint itself gets an objectID too. (I think.)
 
Yeah; I forgot the IndID on the indexes table, to uniquely identify the index...
 
This should work better.

Code: 
SELECT sys.sysobjects.name AS TableName,
       sys.sysindexes.name AS IndexName,
       sys.syscolumns.name AS KeyColumn,
	   sys.index_columns.key_ordinal AS KeyOrdinal,
       CASE sys.index_columns.is_descending_key
         WHEN 1 THEN 'DESC'
         WHEN 0 THEN 'ASC'
       END AS KeyOrder
  FROM sysobjects
  JOIN sys.key_constraints ON parent_object_id = sys.sysobjects.id
  JOIN sys.sysindexes ON sys.sysindexes.id = sys.sysobjects.id and sys.key_constraints.unique_index_id = sys.sysindexes.indid
  JOIN sys.index_columns ON sys.index_columns.object_id = sys.sysindexes.id  and sys.index_columns.index_id = sys.sysindexes.indid
  JOIN sys.syscolumns ON sys.syscolumns.id = sys.sysindexes.id AND sys.index_columns.column_id = sys.syscolumns.colid
 WHERE sys.sysobjects.type = 'u'               -- 'U' means user table
   AND sys.key_constraints.type = 'PK'         -- we're looking for PK constraints
   AND sys.sysobjects.name = 'factory_process' -- the table we want to query
ORDER BY sys.index_columns.key_ordinal
 
Just a note to those who come across this thread in the future: the above query Mike came up with won't work on SQL Server 2000, only SQL Server 2005. I ended up using the "sp_help" stored procedure to get the information I need on SQL Server 2000.
 
Rats, sorry. I can't imagine the SQL 2000 server query would be too different, but I don't have a SQL 2000 instance conveniently available to test on.
 
There's a bunch of meta-information tables that you can use for finding this sort of information.
(It generally involves a lot less messing about than using sysobjects etc.)

Look up INFORMATION_SCHEMA. (I'd do it myself, but it's late here and my after-midnight google-fu is amazingly weak)

It's more likely to be dialect-proof than messing with sysobjects, sysindexes etc.

Code: 
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Customers' 
ORDER BY ORDINAL_POSITION

(I can't for the life of me remember which table holds primary key constraints)
 
Condemned said:
(I can't for the life of me remember which table holds primary key constraints)
Click to expand...
Problem is, there ain't one.

INFORMATION_SCHEMA.KEY_COLUMN_USAGE is close, but it also includes foreign keys. CONSTRAINT_COLUMN_USAGE is close, too, but it includes all constrains, not just key constraints.
 
OK, I've done some poking around....

The table with the last bit of information needed is INFORMATION_SCHEMA.TABLE_CONSTRAINTS. This has a column, CONSTRAINT_TYPE which (amongst others) can contain 'PRIMARY KEY'.

The following query will list all tables and their primary keys.

It's not pretty, but it seems to work.
Can anyone with a non-SQLServer database see if it works for them too?

Code: 
SELECT
	T.TABLE_NAME,
	COALESCE( CU.CONSTRAINT_NAME , '<NO PRIMARY KEY>') AS PRIMARY_KEY_NAME,
	CU.COLUMN_NAME 
FROM INFORMATION_SCHEMA.TABLES AS T
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
	ON	TC.TABLE_CATALOG = T.TABLE_CATALOG
	AND	TC.TABLE_SCHEMA = T.TABLE_SCHEMA
	AND	TC.TABLE_NAME = T.TABLE_NAME
	AND	TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
	ON	CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
	AND	CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
	AND	CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE
	T.TABLE_TYPE = 'BASE TABLE'
ORDER BY
	T.TABLE_NAME,
	CU.ORDINAL_POSITION
 
Seems to work okay for me; but it doesn't report the order of the keys. So if my PK is (A,B), the result of the query might tell me B,A; or it might tell me A,B. I don't see ordering information in the rows.
 
The order of the primary key fields should be the order they are returned in.

INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION gives the order of the primary key, hence the order by clause. You might want to explicity add CU.ORDINAL_POSITION to the selected fields to prove this.

I've just found the following caveat if you're using SQLServer 7 though: KB278387 (msdn) ("ORDINAL_POSITION Column Reflects Incorrect Data")

I'll double-check that it works correctly on SQLServer 2000 when I'm at work tomorrow...
 
Ah, I see. I thought ORDINAL_POSITION was the position within the table, not the position within the key.
 
You must log in or register to reply here.
Back
Top