Re: [CF-Database] SQL to get table of Tables, Columns, and PKs

[Jolly Green Giant <jgg-lists@cfjungle.com>]

Actually, I found some code that works great...
in SQL 2000...

				SELECT column_name, constraint_name
				FROM information_schema.key_column_usage
				WHERE TABLE_NAME = '____tablename_here____'
					AND table_catalog = '____your_catalog_here____'
					AND (
						constraint_name LIKE 'PK_%' 
						OR constraint_name LIKE 'FK_%' 
					)

Now I'm just working on getting a list of indexes that also has the column name in with it...
no luck so far.

This one will return all kinds of useful info on indexes, jsut not the column name.  I have to have that to do my join.


SELECT OBJECT_NAME( i.id ) AS TableName , i.name AS IndexName ,
       CASE INDEXPROPERTY( i.id , i.name , 'IsClustered' )
                WHEN 1 THEN 'Yes'
                ELSE 'No'
       END AS IsClustered ,
       CASE INDEXPROPERTY( i.id , i.name , 'IsUnique' )
                WHEN 1 THEN 'Yes'
                ELSE 'No'
       END AS IsUnique ,
       STATS_DATE( i.id , i.indid ) AS LastUpdatedDate ,
       i.id AS ObjectID , i.indid AS IndexID
FROM sysindexes AS i
WHERE 1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) ,
                 INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,
                 INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) ) And
      OBJECTPROPERTY( i.id , 'IsMSShipped' ) = 0 And
      -- To filter out the text/ntext/image columns in sysindexes
      INDEXPROPERTY( i.id , i.name , 'IndexDepth' ) > 0
ORDER BY TableName , IndexID



>>----------------------------<
>>- see footer for list info -<
>>----------------------------<
>Off the top of my head, I would expect you need to query the table that
>holds the info about your database diagram, and thus the relationships,
>primary keys, foreign keys, triggers etc.
>
>
>> -----Original Message-----
>> From: database-bounces@list.cfdeveloper.co.uk 
>> [mailto:database-bounces@list.cfdeveloper.co.uk] On Behalf Of 
>> Jolly Green Giant
>> Sent: 09 November 2004 19:30
>> To: database@list.cfdeveloper.co.uk
>> Subject: [CF-Database] SQL to get table of Tables, Columns, and PKs
>> 
>> >----------------------------<
>> >- see footer for list info -<
>> >----------------------------<
>> Summary: 
>> I'd like to get help to edit my query so that I see which 
>> columns are primary keys.
>> 
>> Detail:
>> I'm making a bit of a Data Dictionary for our SQL database 
>> and am pulling lots of content from the sys tables.  
>> 
>> The following query will give me (once inside a loop over the 
>> available tables) the columns for each table and data about 
>> the columns.  I'd like to add to it to get a flag if the 
>> field/column is a primary key.  A flag for columns that are 
>> foreign keys would be a huge bonus!
>> 
>> 
>> 
>> 	SELECT  ST.name AS ColumnType,
>> 			SC.name AS ColumnName,
>> 			SC.length AS ColumnLength,
>> 			SC.isnullable,
>> 			SC.colstat,
>> 			SP.Value,
>> 			CO.Text
>> 	FROM	dbo.syscolumns SC
>> 
>> 		INNER JOIN dbo.systypes ST
>> 			ON (SC.xtype = ST.xtype)
>> 		LEFT OUTER JOIN dbo.sysproperties SP
>> 			ON (SC.ID = SP.ID
>> 				AND SC.ColID = SP.smallID)
>> 		LEFT OUTER JOIN dbo.syscomments co
>> 			ON (SC.cdefault = CO.ID)
>> 		LEFT OUTER JOIN dbo.sysindexes si
>> 			ON (sc.colid = si.)
>> 
>> 	WHERE   SC.ID = #tableID#
>> 	ORDER BY SC.ColOrder
>> 	
>> 
>> Now the following query will give me a list of indexes sorted 
>> by the table they're in.  Howver, the sysindexes table 
>> doesn't seem to have an easy direct link back to the 
>> syscolumns table as to which columns are involved in the 
>> index.  sysindexes.keys, according to the docs contains 
>> &quot;a list of the column ids of the columns that make up 
>> the index key.&quot;  The output of that keys field resembles...
>> 
>> 0x3801380004000A0000000000000000....
>> 
>> I did a join on the syscolumns table with an IN operator, but 
>> that got me only blank cells.
>> 
>> select 
>> 	o.name as 'Table', 
>> 
>> 	i.name as 'Index',
>> 	case indid
>> 		when 0 then 'Primary Key'
>> 		when 1 then 'Clustered'
>> 		else 'Non-clustered'
>> 		end as 'IndexType',
>> 	i.keys
>> from 
>> 	sysindexes i join sysobjects o 
>> 
>> 		on o.id = i.id
>> order by 1,indid,2
>> --
>> For details on ALL mailing lists and for joining or leaving 
>> lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
>> 
>> --
>> CFDeveloper Sponsors:-
>> >- Hosting provided by www.cfmxhosting.co.uk -<
>> >- Forum provided by www.fusetalk.com -<
>> >- DHTML Menus provided by www.APYCOM.com -<
>> >- Lists hosted by www.Gradwell.com -<
>> >- CFdeveloper is run by Russ Michaels, feel free to 
>> volunteer your help 
>> >-<
>> 
>
>
>--
>For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
>
>--
>CFDeveloper Sponsors:-
>>- Hosting provided by www.cfmxhosting.co.uk -<
>>- Forum provided by www.fusetalk.com -<
>>- DHTML Menus provided by www.APYCOM.com -<
>>- Lists hosted by www.Gradwell.com -<
>>- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
>
>