- /*
- 查找数据库指定数据的数据表和字段名称SQL语句
- 方法一:
- */
- declare @str varchar(100)
- set @str='11111111111' --要搜索的字符串
- declare @s varchar(8000)
- declare tb cursor local for
- select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
- print '' ['+b.name+'].['+a.name+']'''
- from syscolumns a join sysobjects b on a.id=b.id
- where b.xtype='U' and a.status>=0
- and a.xusertype in(175,239,231,167)
- open tb
- fetch next from tb into @s
- while @@fetch_status=0
- begin
- exec(@s)
- fetch next from tb into @s
- end
- close tb
- deallocate tb
- /*方法二:*/
- CREATE PROCEDURE [dbo].[SP_FindValueInDB]
- (
- @value VARCHAR(1024)
- )
- AS
- BEGIN
- -- SET NOCOUNT ON added to prevent extra result sets from
- -- interfering with SELECT statements.
- SET NOCOUNT ON;
- DECLARE @sql VARCHAR(1024)
- DECLARE @table VARCHAR(64)
- DECLARE @column VARCHAR(64)
- CREATE TABLE #t (
- tablename VARCHAR(64),
- columnname VARCHAR(64)
- )
- DECLARE TABLES CURSOR
- FOR
- SELECT o.name, c.name
- FROM syscolumns c
- INNER JOIN sysobjects o ON c.id = o.id
- WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
- ORDER BY o.name, c.name
- OPEN TABLES
- FETCH NEXT FROM TABLES
- INTO @table, @column
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
- SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '
- SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
- SET @sql = @sql + @column + ''')'
- EXEC(@sql)
- FETCH NEXT FROM TABLES
- INTO @table, @column
- END
- CLOSE TABLES
- DEALLOCATE TABLES
- SELECT *
- FROM #t
- DROP TABLE #t
- END
- exec SP_FindValueInDB '要查找的字符串'