|
ULT '+coalesce(d.[definition],'')
else ''
end
from sys.columns c
cross apply (
select DataType=type_name(c.user_type_id)
,UserDefinedFlag=case
when c.system_type_id=c.user_type_id
then 0
else 1
end) F1
left join sys.default_constraints d ON c.default_object_id=d.[object_id]
left join sys.computed_columns k ON c.[object_id]=k.[object_id]
and c.column_id=k.column_id
left join sys.xml_schema_collections x ON c.xml_collection_id = x.xml_collection_id
)
,IndexDefs as
(
select TableObj=i.[object_id]
,IxName=quotename(i.name)
,IxPKFlag=i.is_primary_key
,IxType=case when i.is_primary_key=1 then 'PRIMARY KEY ' when i.is_unique=1 then 'UNIQUE ' else '' end
+lower(type_desc)
,IxDef='('+IxColList+')'
+coalesce(' INCLUDE ('+IxInclList+')','')
,IxOpts=IxOptList
from sys.indexes i
left join sys.stats s ON i.index_id=s.stats_id and i.[object_id]=s.[object_id]
cross apply (
select stuff((select case when i.is_padded=1 then ', PAD_INDEX=ON' else '' end
+case when i.fill_factor<>0 then ', FILLFACTOR='+cast(i.fill_factor as varchar(10)) else '' end
+case when i.ignore_dup_key=1 then ', IGNORE_DUP_KEY=ON' else '' end
+case when s.no_recompute=1 then ', STATISTICS_RECOMPUTE=ON' else '' end
+case when i.allow_row_locks=0 then ', ALLOW_ROW_LOCKS=OFF' else '' end
+case when i.allow_page_locks=0 then ', ALLOW_PAGE_LOCKS=OFF' else '' end)
,1,2,'')) F_IxOpts(IxOptList)
cross apply (
select stuff((select ','+quotename(c.name)
+case
when ic.is_descending_key=1 AND i.type<>3
then ' DESC'
WHEN ic.is_descending_key=0 AND i.type<>3
THEN ' ASC'
ELSE ''
end
from sys.index_columns ic
join sys.columns c ON ic.[object_id]=c.[object_id]
and ic.column_id=c.column_id
where ic.[object_id]=i.[object_id]
and ic.index_id=i.index_id
and ic.is_included_column=0
order by ic.key_ordinal
FOR xml path(''),type).value('.','nvarchar(max)')
,1,1,'')) F_IxCols(IxColList)
cross apply (
select stuff((select ','+quotename(c.name)
from sys.index_columns ic
join sys.columns c ON ic.[object_id]=c.[object_id]
and ic.column_id=c.column_id
where ic.[object_id]=i.[object_id]
and ic.index_id=i.index_id
and ic.is_included_column=1
order by ic.key_ordinal
FOR xml path(''),type).value('.','nvarchar(max)')
,1,1,'')) F_IxIncl(IxInclList)
where i.type_desc<>'HEAP'
)
,FKDefs as
(
select TableObj=f.parent_object_id
,FKName=quotename(f.name)
,FKRef=quotename(object_schema_name(f.referenced_object_id))+'.'
+quotename(object_name(f.referenced_object_id))
,FKColList=ParentColList
,FKRefList=RefColList
,FKDelOpt=case f.delete_referential_action
when 1 then 'CASCADE'
when 2 then 'SET NULL'
when 3 then 'SET DEFAULT'
end
,FKUpdOpt=case f.update_referential_action
when 1 then 'CASCADE'
when 2 then 'SET NULL'
when 3 then 'SET DEFAULT'
end
,FKNoRepl=f.is_not_for_replication
from sys.foreign_keys f
cross apply (
select stuff((select ','+quotename(c.name)
from sys.foreign_key_columns k
join sys.columns c ON k.parent_object_id=c.[object_id]
and k.parent_column_id=c.column_id
where k.constraint_object_id=f.[object_id]
order by constraint_column_id
FOR xml path(''),type).value('.','nvarchar(max)')
,1,1,'')) F_Parent(ParentColList)
cross apply (
selec |