Monday, December 13, 2004

SQL-Server schema query

Here's a SQL query I use everytime I need to regenerate my stored procedures or entities and I thought you may find it useful. The following statement is used to fill a dataset with two tables (two result sets are returned). Relate the two tables on table_name and set Nested=True on the relation. Then call WriteXml on the dataset to get an XML doc of your entire database schema. Use that in conjunction with your pretty little stylesheets and you can code generate anything that uses schema information from your database.

SELECT DISTINCT c.table_name
  FROM information_schema.columns c
  INNER JOIN information_schema.tables t ON c.table_name = t.table_name
  WHERE (t.table_type = 'BASE TABLE' and not (t.table_name = 'dtproperties'))
  ORDER BY c.table_name;
SELECT
  c.table_name,
  c.column_name,
  c.data_type,
  CASE c.is_nullable WHEN 'YES' THEN 1 ELSE 0 END AS is_nullable,
  c.column_default,
  c.character_maximum_length,
  c.numeric_precision,
  c.numeric_scale,
  c.datetime_precision,
  COALESCE ( (
    SELECT CASE cu.column_name WHEN null THEN 0 ELSE 1 END
      FROM information_schema.constraint_column_usage cu
      INNER join information_schema.table_constraints ct ON ct.constraint_name = cu.constraint_name
      WHERE ct.constraint_type = 'PRIMARY KEY' AND
            ct.table_name = c.table_name AND
            cu.column_name = c.column_name
  ),0) AS IsPrimaryKey
  FROM information_schema.columns c
  INNER JOIN information_schema.tables t ON c.table_name = t.table_name
  WHERE (t.table_type = 'BASE TABLE' AND NOT (t.table_name = 'dtproperties'))
  ORDER BY c.table_name, c.ordinal_position;

1 comment:

Rick Strahl said...

Nice one! I've been using something way more complex using various stored procs... this definitely is a lot easier to get the key info out!