ms sql server 生成表的视图

--select * from sys.types



declare @table_name nvarchar(100)
set @table_name='EC_Product'
if exists (select 1 from sys.tables where name=@table_name)
begin
declare @table_info table(id int identity(1,1) not null,info nvarchar(300))
insert into @table_info select 'create view V_'+@table_name+'_Diff'
insert into @table_info select 'as' 
insert into @table_info
select 'select '
insert into @table_info
select case when isnullable=0 then tablename+','       else tablename+','+default_value+') as '+name+','  end 
from (select a.isnullable,a.name,b.xtype,case when a.isnullable =0 then '  ['+a.name+']' else '  ISNULL('+a.name end as tablename,case when b.xtype in (48,52,56,127) then '100' 
--tinyint\smallint\int\bigint     when b.xtype in (59,60,62,106,108) then '1.01'
--real\money\float\decimal\numeric     

when b.xtype=40 then '''1900-01-01''' --date     
when b.xtype=41 then '''01:00:01''' --time     
when b.xtype=58 then '''1900-01-01 01:01:01''' --smalldatetime     
when b.xtype=61 then '''1900-01-01 01:01:01.001''' --datetime   
when b.xtype in(60,62) then '0.0' --money 
when b.xtype in(104,108) then '1' --money     
else '''@#''' end as default_value
from syscolumns a,systypes b   where a.id = OBJECT_ID(@table_name) and a.xtype = b.xusertype    )a where xtype not in(34,35,99,165,173,189,241)
--xml\timestamp\binary\varbinary\ntext\text\image
update @table_info set info=SUBSTRING(info,1,len(info)-1) where id=(select MAX(id) from @table_info)
insert into @table_info values('from dbo.['+@table_name+'] with(nolock)')
select info from @table_info
end
else  
print('Can not find the table '+@table_name)

ms sql server 生成表的视图，在使用 tabdiff 比较部分字段的时候使用