,IxPKFlag=i.is_primary_key

,IxType=casewhen i.is_primary_key=1then 'PRIMARY KEY 'when i.is_unique=1then 'UNIQUE ' else '' end

+lower(type_desc)

,IxDef='('+IxColList+')'

+coalesce(' INCLUDE ('+IxInclList+')','')

,IxOpts=IxOptList

from sys.indexesi

left joinsys.statss ON i.index_id=s.stats_idand i.[object_id]=s.[object_id]

cross apply(

select stuff((selectcase when i.is_padded=1then ', PAD_INDEX=ON'else '' end

+casewheni.fill_factor<>0then ', FILLFACTOR='+cast(i.fill_factoras varchar(10))else '' end

+casewheni.ignore_dup_key=1then ', IGNORE_DUP_KEY=ON'else'' end

+casewhens.no_recompute=1then ',STATISTICS_RECOMPUTE=ON'else '' end

+casewheni.allow_row_locks=0then ', ALLOW_ROW_LOCKS=OFF'else'' end

+casewheni.allow_page_locks=0then ', 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=1AND i.type<>3

then ' DESC'

WHEN ic.is_descending_key=0AND i.type<>3

THEN ' ASC'

ELSE ''

end

fromsys.index_columnsic

joinsys.columnsc ON ic.[object_id]=c.[object_id]

andic.column_id=c.column_id

whereic.[object_id]=i.[object_id]

andic.index_id=i.index_id

andic.is_included_column=0

orderbyic.key_ordinal

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,1,''))F_IxCols(IxColList)

cross apply(

select stuff((select','+quotename(c.name)

fromsys.index_columnsic

joinsys.columnsc ON ic.[object_id]=c.[object_id]

andic.column_id=c.column_id

whereic.[object_id]=i.[object_id]

andic.index_id=i.index_id

andic.is_included_column=1

orderbyic.key_ordinal

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,1,''))F_IxIncl(IxInclList)

where i.type_desc<>'HEAP'

)

,FKDefsas

(

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=casef.delete_referential_action

when1 then 'CASCADE'

when2 then 'SET NULL'

when3 then 'SET DEFAULT'

end

,FKUpdOpt=casef.update_referential_action

when1 then 'CASCADE'

when2 then 'SET NULL'

when3 then 'SET DEFAULT'

end

,FKNoRepl=f.is_not_for_replication

from sys.foreign_keysf

cross apply(

select stuff((select','+quotename(c.name)

fromsys.foreign_key_columnsk

joinsys.columnsc ON k.parent_object_id=c.[object_id]

and k.parent_column_id=c.column_id

wherek.constraint_object_id=f.[object_id]

orderbyconstraint_column_id

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,1,''))F_Parent(ParentColList)

cross apply(

select stuff((select','+quotename(c.name)

fromsys.foreign_key_columnsk

joinsys.columnsc ON k.referenced_object_id=c.[object_id]

and k.referenced_column_id=c.column_id

wherek.constraint_object_id=f.[object_id]

orderbyconstraint_column_id

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,1,''))F_Ref(RefColList)

)

select TableName

,[definition]

from sys.tablest

cross apply(

select TableName=quotename(object_schema_name(t.[object_id]))+'.'

+quotename(object_name(t.[object_id])))F_Name

cross apply(

select stuff((select@crlf+' ,'+ColumnDef

fromColumnDefs

whereTableObj=t.[object_id]