Gather Meta Data

[anuu]

Hi,

I would like to prepare a data dictionary for my database (northwind).

I have framed the below SQL

SELECT
'NAME ' = a.name,
'DESCRIPTION' = b.value,
'Type ' = type_name(a.xusertype),
' ' AS 'Values',
'NULL ' = case when a.isnullable = 0 then ' ' else 'X' end,
' ' AS 'PK',
' ' AS 'FK'
FROM
syscolumns a,
sysproperties b

WHERE
a.id = 2073058421 AND --- Customers Table
a.number = 0 AND
b.id = a.id AND
b.smallid = a.colid

ORDER BY a.colid

and the output would be:

NAME DESCRIPTION Type Values NULL PK FK
-------------- --------------- ---------- ------ ----- ---- ----
CustomerID Customer ID nchar
CompanyName Company Name nvarchar
ContactName Contact Name nvarchar X
ContactTitle Contact Title nvarchar X
Address Address nvarchar X
City City nvarchar X
Region Region nvarchar X
PostalCode Postal Code nvarchar X
Country Country nvarchar X
Phone Phone # nvarchar X
Fax Fax # nvarchar X

PK and FK is where I need to print whether the column is Primary Key or
Foreign Key.

If CustomerId is defined as Primary Key then PK should have X printed.
Thats the objective.

How will I accomplish this ?
Thanks in advance,
Anu

[Razvan Socol]

The following query is close to your requirements:

SELECT c.name AS ColumnName,
p.value AS ColumnDescription,
TYPE_NAME(c.xusertype) AS Type,
CASE WHEN c.isnullable=0 THEN ' ' ELSE 'X' END AS Nullable,
CASE WHEN EXISTS (
SELECT * FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysindexkeys k ON i.id=k.id AND i.indid=k.indid
WHERE o.xtype='PK' AND k.id=c.id AND k.colid=c.colid
) THEN 'X' ELSE ' ' END AS PK,
CASE WHEN EXISTS (
SELECT * FROM sysforeignkeys f
WHERE c.id=f.fkeyid AND c.colid=f.fkey
) THEN 'X' ELSE ' ' END AS FK
FROM syscolumns c
LEFT JOIN sysproperties p on p.id=c.id AND p.smallid = c.colid AND
p.name='MS_Description'
WHERE c.id = OBJECT_ID('Orders')
ORDER BY c.colid

with the following observations:

1. A column is not "defined as" primary key. It is "a part of" the
primary key (see the "Order Details" table in Northwind, which has a
compound primary key). The same goes for foreign keys (it is possible
to have a compound foreign key, although this is rarely encountered).

2. The sysproperties table should be LEFT JOIN-ed to the other tables
(because if you use an INNER JOIN, the columns that do not have a
description will be omitted). We should also filter for
p.name='MS_Description' because there may be also other properties in
the sysproperties table. Moreover, it should be noted that this table
is undocumented and we should use the fn_listextendedproperty function
(because using undocumented tables may cause problems in later versions
of SQL Server).

3. Use the OBJECT_ID function instead of using directly the ID of an
object.

4. Use ANSI-style joins, where the join condition is clearly placed in
the ON clause (instead of putting it in the WHERE clause, with the
other conditions).

5. I have no idea what you will put in the "Values" column (this column
seems very odd to me).

Razvan

[Mark D Powell]

Razvan, nice query. Perhaps for Anuu's value column you could have
provided the Default Value when one existed. That is the only "value"
that would seem to make any sense.

IMHO -- Mark D Powell --

[anuu]

Thanks, Razvan. The query is pretty simple and delivers what I need.