April 12, 2007 at 8:15 am
Hi ALL,
I have the follwoing 2 SQL Statements
select
distinct(obj.name) as tableName, obj.type_desc, col.name as columnName, inf.data_type ,
col
.max_length, inf.is_nullable, inf.Numeric_precision, inf.numeric_scale,
inf
.datetime_precision
from
sys.all_objects as obj, information_schema.columns as inf,
sys.all_columns
as col
where
obj.object_id = col.object_id
and inf.table_name = obj.name
and inf.column_name = col.name
and
obj.type_desc <> 'INTERNAL_TABLE'
and
obj.type_desc <> 'SQL_INLINE_TABLE_VALUED_FUNCTION'
and
obj.type_desc <> 'SQL_TABLE_VALUED_FUNCTION'
order
by obj.type_desc
select
con.constraint_name, con.Constraint_type from information_schema.columns as inf
left
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS as Con on inf.table_name = con.table_name
Both work fine on their own, however the output on the second statement I want to combine with the first. Can anybody help out. Thanks.
April 13, 2007 at 7:52 am
Try this:
select
distinct(obj.name) as tableName
,obj.type_desc
,col.name as columnName
,inf.data_type
,col.max_length
,inf.is_nullable
,inf.Numeric_precision
,inf.numeric_scale
,inf.datetime_precision
,con.constraint_name
, con.Constraint_type
from
sys.all_objects obj
left
outer join information_schema.columns inf on
obj
.name = inf.table_name
left
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS con on
inf
.table_name = con.table_name
left
outer join sys.all_columns col on
inf
.column_name = col.name
where
obj.object_id = col.object_id
and inf.table_name = obj.name
and inf.column_name = col.name
and obj.type_desc <> 'INTERNAL_TABLE'
and obj.type_desc <> 'SQL_INLINE_TABLE_VALUED_FUNCTION'
and obj.type_desc <> 'SQL_TABLE_VALUED_FUNCTION'
order
by obj.type_desc
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply