sql-server - 如何针对多个数据库表验证变量

Question

有谁知道如何针对所有数据库表中存储相同类型信息的列检查变量？我有一个设计不良的数据库，它在一个数据库中的60多个表中存储ssn。各个表中列的一些变体包括:

app_ssn

ca_ssn

cand_ssn

crl_ssn

cu_ssn

emtaddr_ssn

re_ssn

sfcart_ssn

sfordr_ssn

Socsecno

n

n

社交网络

我想创建一个存储过程，该过程将接受一个值并针对名称中带有'ssn'的每个表进行检查。

Answer 1

-我假设表格/列名不需要用方括号括起来。您可能想将匹配项保存在表格中-我只是选择它们。我还假设ssn是一个字符。

alter proc  proc1 
@search1 varchar(500)
as
begin
set nocount on

declare @strsql varchar(500)
declare @curtable sysname
declare @prevtable sysname
declare @column sysname

select top 1 @curtable= table_schema+'.'+table_name, @column=column_name
from INFORMATION_SCHEMA.COLUMNS  
where CHARINDEX('ssn',column_name) > 0
order by table_schema+'.'+table_name +column_name

-- make sure that at least one column has ssn in the column name
if @curtable is not null
begin
while (1=1)
begin
set @strsql = 'select * from ' +@curtable +' where '+''''+@search1+''''+ ' = '+@column
print @strsql

-传递给ssn的任何匹配项都将在此处匹配...

exec (@strsql)

set @prevtable = @curtable+@column
select top 1 @curtable= table_schema+'.'+table_name, @column=column_name
from INFORMATION_SCHEMA.COLUMNS  
where CHARINDEX('ssn',column_name) > 0
and table_schema+'.'+table_name +column_name> @prevtable
order by table_schema+'.'+table_name +column_name
-- when we run out of columns that contain ssn we are done...
if @@ROWCOUNT = 0
    break

end

end

end