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

,1,5,''))F_Cols(ColumnList)

cross apply(

select stuff((select@crlf+' ,CONSTRAINT '+quotename(name)+' CHECK '

+casewhen is_not_for_replication=1then'NOT FORREPLICATION ' else'' end

+coalesce([definition],'')

fromsys.check_constraints

whereparent_object_id=t.[object_id]

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

,1,2,''))F_Const(ChkConstList)

cross apply(

select stuff((select@crlf+' ,CONSTRAINT '+IxName+' '+IxType+' '+IxDef+coalesce(' WITH ('+IxOpts+')','')

fromIndexDefs

whereTableObj=t.[object_id]

andIxPKFlag=1

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

,1,2,''))F_IxConst(IxConstList)

cross apply(

select stuff((select@crlf+' ,CONSTRAINT '+FKName+' FOREIGN KEY '+'('+FKColList+')'+' REFERENCES '+FKRef+' ('+FKRefList+')'

+casewhen FKDelOpt is NOT NULLthen ' ON DELETE '+FKDelOptelse '' end

+casewhen FKUpdOpt is NOT NULLthen ' ON UPDATE '+FKUpdOptelse '' end

+casewhen FKNoRepl=1then ' NOT FOR REPLICATION'else'' end

fromFKDefs

whereTableObj=t.[object_id]

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

,1,2,''))F_Keys(FKConstList)

cross apply(

select stuff((select@crlf+'CREATE '+IxType+' INDEX '+IxName+' ON '+TableName+' '+IxDef+coalesce(' WITH ('+IxOpts+')','')

fromIndexDefs

whereTableObj=t.[object_id]

andIxPKFlag=0

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

,1,2,''))F_Indexes(IndexList)

cross apply(

select [definition]=(select'CREATE TABLE '+TableName+@crlf+'('+@crlf+' '+ColumnList+coalesce(@crlf+ChkConstList,'')+coalesce(@crlf+IxConstList,'')+coalesce(@crlf+FKConstList,'')+@crlf+')'+coalesce(@crlf+IndexList,'')+@crlf

FOR xmlpath(''),type).value('.','nvarchar(max)'))F_Link

3、查询出SQL中数据库中所有视图，函数，存储过程触发器脚本

SELECT QUOTENAME(object_schema_name(m.object_id))+'.'+QUOTENAME(object_name(m.object_id))AS [name],o.type,m.definition

FROM sys.sql_modulesm INNER JOIN sys.objectso ON m.object_id= o.object_id

4、查询出SQL中数据库中某个表中数据的Insert语句

set nocounton

declare @table_name varchar(100)

declare @table_full_name varchar(100)

declare @sql nvarchar(max)

declare @sqlvalues nvarchar(max)

declare @identity int

DECLARE S_Cursor CURSOR FOR

SELECT o.nameasname, '[' + s.name + '].[' + o.name + ']' as full_name

FROM sys.objectso inner join sys.schemass on o.schema_id= s.schema_id

where o.name='Product'

and s.name='dbo'

and o.type='U'

order byo.name

OPEN S_Cursor

FETCH NEXTFROM S_Cursor INTO @table_name,@table_full_name

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql= ' ('

set @sqlvalues= 'values (''+'

set @identity= 0

select @sqlvalues = @sqlvalues+ col + ' + '','' + ',@sql = @sql + '[' + name + '],', @identity= @identity + is_identity

from (selectcase

when t.name = 'varchar' then'case when ['+ c.name +'] is null Then ''null'' Else '+''''''''' + ' + 'replace(['+c.name+'],'''''''','''''''''''')' + '+'''''''''+' End'

when t.name = 'nvarchar' Then 'case When ['+ c.name +'] Is Null Then ''null'' Else '+'''N'''''' + ' + 'replace(['+ c.name+'],'''''''','''''''''''')' + '+'''''''''+' End'

when t.name = 'char' Then 'case When ['+ c.name +'] Is Null Then ''null'' Else '+''''''''' + ' + 'cast(Replace(['+ c.name+'],'''''''','''''''''''') As Char(' + Cast(c.max_length As Varchar) + '))+'''''''''+' End'