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.
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
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