SqlServer查询所有表默认值不为空

create table #a(table_name varchar(500),column_name varchar(500),default_value varchar(500))

declare my_cursor cursor for

select a.name FROM sysobjects AS a WHERE ( a.type = 'u' ) order by name

open my_cursor

declare @tableName varchar(100)

declare @index int

set @index=1

fetch next from my_cursor into @tableName

while @@FETCH_STATUS =0

begin

print(cast(@index as varchar)+'--'+@tableName)

insert into #a

SELECT SO.NAME AS Table_Name, SC.NAME AS Column_Name, SM.TEXT AS Default_Value

FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id

LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id

WHERE SO.xtype = 'U' and so.name=@tableName and SM.TEXT is not null

ORDER BY SO.[name], SC.colid

set @index=@index+1

fetch next from my_cursor into @tableName --获取下一条数据并复制给变量

end

close my_cursor

deallocate my_cursor

select * from #a

drop table #a