Determine primary keys and unique keys for all tables in a database...

SQL Server Engine Tips Determine primary keys and unique keys for all | patio set

Fri, 19 Jun 2009 13:19:39 GMT

PingBack from http://patiosetsite.info/story.php?id=1006

T-SQL: Determine primary keys and unique keys for all tables in a database...

Little Tidbits of Random Knowledge — Thu, 14 Sep 2006 23:58:54 GMT

T-SQL: Determine primary keys and unique keys for all tables in a database...

Little Tidbits of Random Knowledge — Thu, 14 Sep 2006 23:26:44 GMT

re: Determine primary keys and unique keys for all tables in a database...

Manfred Mejías — Thu, 14 Sep 2006 22:29:09 GMT

thks :o)

re: Determine primary keys and unique keys for all tables in a database...

SQL Server Engine Team — Thu, 14 Sep 2006 21:46:42 GMT

The INFORMATION_SCHEMA.KEY_COLUMN_USAGE is a known issue / bug for SQL Server 2000. We have fixed it in SQL Server 2005. You will have to workaround it in SQL Server 2000 by writing queries against the system tables instead.

--
Umachandar

re: Determine primary keys and unique keys for all tables in a database...

Manfred Mejías — Thu, 14 Sep 2006 21:30:49 GMT

With Sqlserver 2000 we have a little problem if we don't use dbo user. For example, we have two user with SQL Server authentication, Manfred and Alexis and create the "Sales" database. So, we have the table Deparment in each user. If the user dbo uses the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view it don't see the primary keys, but, if we use the user Manfred we can see it.

We edit the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view and changed one line index_col(t_obj.name,i.indid,v.number) with index_col(user_name(t_obj.uid)+'.'+t_obj.name,i.indid,v.number) and it works, why do you think?

re: Determine primary keys and unique keys for all tables in a database...

Chris — Thu, 10 Aug 2006 18:30:40 GMT

Great! Thanks! This is exactly what I was looking for. I like that it just works and I don't have to understand it ;)