SQL Server Table column 查找

1. 單純的一個DB 查找 Script : 

SELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_nameFROM sys.tables AS tINNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_IDWHERE c.name LIKE '%XXX%'ORDER BY schema_name, table_name;

2. 整個主機 查找 包含 XXX 欄位的 Table Script : 

declare @tmp_record table(db_name varchar(100),

schema_name varchar(100),table_name varchar(100),

column_name varchar(100),max_length varchar(100))

declare @sqlstr nvarchar(4000)

declare @keyword varchar(20)

declare @db_name sysname

set @keyword = 'acc_no'

declare cur_db cursor for

select name from sys.databases where state = 0

open cur_db

fetch next from cur_db into @db_name

while @@fetch_status = 0

begin

set @sqlstr = ''

set @sqlstr = @sqlstr + '

SELECT '''+@db_name+''',c.name as schema_name,b.name as table_name,a.name as column_name,max_length FROM (

select * from ['+@db_name+'].sys.all_objects where object_id IN (

select object_id from ['+@db_name+'].sys.all_columns where name like ''%'+@keyword+'%''

)

) b

RIGHT JOIN ['+@db_name+'].sys.all_columns a ON a.object_id = b.object_id

LEFT JOIN ['+@db_name+'].sys.schemas c ON c.schema_id = b.schema_id

WHERE a.name like ''%'+@keyword+'%''

and b.type_desc = ''USER_TABLE'' '

begin try

set nocount on

insert into @tmp_record

exec sp_executesql @sqlstr

end try

begin catch

print error_message()

print @sqlstr

end catch

fetch next from cur_db into @db_name

end

close cur_db

deallocate cur_db

select * from @tmp_record