[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
RE: [CF-Database] SQL to get table of Tables, Columns, and PKs
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
> "a list of the column ids of the columns that make up
> the index key." 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
> >-<
>