SQL Server - Catalog Queries 2005/2008

Server 2005 Catalog Query - Get Info for Create Table

This query will return all the necessary information that is used to generate the "create table" syntax for creating a SQL Server table. It is also possible with this information to create code generators for creating stored procedures, views, select, insert, update, and delete statements.

Query

SELECT
 all_objects.object_id,
 all_objects.name as 'Table',
 all_columns.column_id as 'Col ID',
 all_columns.name as 'Column',
 systypes.name as 'Type',
 all_columns.max_length as 'Max Length',
 all_columns.precision as 'Precision',
 all_columns.is_nullable as 'Nullable',
 index_columns.index_id,
 CHECK_CONSTRAINTS.definition as 'checkconstraint',
 default_constraints.type_desc as 'default_constraints_type_desc',
 default_constraints.definition as 'default_constraints_definition'
FROM       SYS.all_objects 
      join SYS.all_columns on all_columns.object_id 
                              = all_objects.object_id
      join SYS.systypes on all_columns.system_type_id 
                        = systypes.xtype
 left join sys.index_columns on index_columns.index_column_id 
                             = all_columns.column_id and
                                index_columns.object_id 
                                = all_columns.object_id and
                                index_columns.index_id = 1
 left join SYS.CHECK_CONSTRAINTS 
      on CHECK_CONSTRAINTS.parent_object_id 
      =  all_objects.object_id
     and CHECK_CONSTRAINTS.parent_column_id
     = all_columns.column_id                            
 left join SYS.default_constraints 
      on default_constraints.parent_object_id 
      =  all_objects.object_id
      and default_constraints.parent_column_id 
      = all_columns.column_id 
where all_objects.name like '%ClassWaitlist%' and
      all_objects.type = 'U'     
order by all_objects.name, all_columns.column_id

Using this query

You will need to modify the line "where all_objects.name like '%ClassWaitlist%'" for the SQL table your want to return data for.

 Collapse All    Expand All
Custom Search

SQL Server Information and Resources