t stuff((select ','+quotename(c.name)
from sys.foreign_key_columns k
join sys.columns c ON k.referenced_object_id=c.[object_id]
and k.referenced_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_Ref(RefColList)
)
select TableName
,[definition]
from sys.tables t
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
from ColumnDefs
where TableObj=t.[object_id]
order by ColSeq
FOR xml path(''),type).value('.','nvarchar(max)')
,1,5,'')) F_Cols(ColumnList)
cross apply (
select stuff((select @crlf+' ,CONSTRAINT '+quotename(name)+' CHECK '
+case when is_not_for_replication=1 then 'NOT FOR REPLICATION ' else '' end
+coalesce([definition],'')
from sys.check_constraints
where parent_object_id=t.[object_id]
FOR xml path(''),type).value('.','nvarchar(max)')
,1,2,'')) F_Const(ChkConstList)
cross apply (
select stuff((select @crlf+' ,CONSTRAINT '+IxName+' '+IxType+' '+IxDef+coalesce(' WITH ('+IxOpts+')','')
from IndexDefs
where TableObj=t.[object_id]
and IxPKFlag=1
FOR xml path(''),type).value('.','nvarchar(max)')
,1,2,'')) F_IxConst(IxConstList)
cross apply (
select stuff((select @crlf+' ,CONSTRAINT '+FKName+' FOREIGN KEY '+'('+FKColList+')'+' REFERENCES '+FKRef+' ('+FKRefList+')'
+case when FKDelOpt is NOT NULL then ' ON DELETE '+FKDelOpt else '' end
+case when FKUpdOpt is NOT NULL then ' ON UPDATE '+FKUpdOpt else '' end
+case when FKNoRepl=1 then ' NOT FOR REPLICATION' else '' end
from FKDefs
where TableObj=t.[object_id]
FOR xml path(''),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+')','')
from IndexDefs
where TableObj=t.[object_id]
and IxPKFlag=0
FOR xml path(''),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 xml path(''),type).value('.','nvarchar(max)')) F_Link
这个SQL语法是网上某位大神的，我收藏了很久了都忘记在哪里了，这里不能提供出处，望见谅。
在此处也一样的编写一个方法来执行上述SQL 返回一个datatable
做好上述准备以后我们就可以来实现 表结构比对方法了，此时我们需要考虑一下几点：
1.目标数据库中无来源数据库中表的情况下，获取创建表语法
2.目标数据库中存在来源数据库中表的情况下，进行表字段比较
3.综合第2点，目标数据表中无来源数据表中的字段，则在目标数据表中添加来源表的字段
满足上述这些要求我们就可以设计出一个比对方法了。接下来提供数据库表字段的添加SQL语法和字段描述的SQL语法如下：
--添加数据库字段的语法
strbSQLScript.Append("IF NOT EXISTS(select * from syscolumns where id=object_id('" + dr["表名"].ToString() + "') and name='" + dr["字段名"].ToString() + "') \r\n");
strbSQLScript.Append("Alter table " + dr["表名"].ToString() + " Add " + dr["字段名"].ToString() + " " + dr["字段类型"].ToString() + "(" + dr["字段长度"].ToString() + ") null \r\n");
strbSQLScript.Append("GO \r\n");
--添加数据库字段描述的语法
strbSQLScript.Append("IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'" + dr["表名"].ToString() + "', N'COLUMN',N'