代码之家 › 专栏 › 技术社区 › NitroxDM

SQL来搜索整个MS SQL 2000数据库中的值

sql

NitroxDM · 技术社区 · 17 年前

我想在整个MS SQL 2000数据库中搜索一个值。这只是为了帮助发展。在考虑这个问题时,请记住这一点。

这将获得我要查找的所有表名和数据类型的列:

SELECT Columns.COLUMN_NAME, tables.TABLE_NAME
FROM INFORMATION_SCHEMA.Columns as Columns 
JOIN INFORMATION_SCHEMA.TABLES as tables 
On Columns.TABLE_NAME = tables.TABLE_NAME
WHERE Columns.DATA_TYPE = 'INT'

-- Vars
DECLARE @COUNTER INT
DECLARE @TOTAL INT
DECLARE @TABLE CHAR(128)
DECLARE @COLUMN CHAR(128)
DECLARE @COLUMNTYPE CHAR(128)
DECLARE @COLUMNVALUE INT

-- What we are looking for
SET @COLUMNTYPE = 'INT'
SET @COLUMNVALUE = 3
SET @COUNTER = 0

-- Find out how many possible columns exist
SELECT @TOTAL = COUNT(*)  
FROM INFORMATION_SCHEMA.Columns as Columns 
JOIN INFORMATION_SCHEMA.TABLES as tables 
On Columns.TABLE_NAME = tables.TABLE_NAME
WHERE Columns.DATA_TYPE = @COLUMNTYPE
PRINT CAST(@TOTAL AS CHAR) + 'possible columns'
WHILE @COUNTER < @TOTAL
BEGIN
    SET @COUNTER = @COUNTER +1
    -- ADD MAGIC HERE
END

有什么想法吗?

使现代化 tool 这很有效。

2 回复 | 直到 17 年前

Marc Gravell 17 年前

由于它只是开发工具(而且可能不需要非常优雅),那么使用TSQL生成一堆TSQL,然后复制回查询窗口并执行如何?

SELECT 'SELECT * FROM [' + tables.TABLE_NAME + '] WHERE ['
       + Columns.Column_Name + '] = ' + CONVERT(varchar(50),@COLUMNVALUE)
FROM INFORMATION_SCHEMA.Columns as Columns
INNER JOIN INFORMATION_SCHEMA.TABLES as tables 
    On Columns.TABLE_NAME = tables.TABLE_NAME
WHERE Columns.DATA_TYPE = @COLUMNTYPE

它不会很漂亮,但它应该能起作用。。。另一种方法可能是在表变量中插入类似于上述内容的内容,然后使用 EXEC (@Sql)

Jonathan 13 年前

我发现这个脚本很有帮助。。。但正如马克所说,这并不值得。自从六个月前我写这本书以来,我只使用过几次。

它只会真正派上用场,因为在我们的开发环境中,有几个表在查询它们时会导致绑定错误,而我总是忘记哪些表。

BEGIN TRAN

declare @search nvarchar(100)
set @search = 'string to search for'

-- search whole database for text
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF nullif(object_id('tempdb..#tmpSearch'), 0) IS NOT NULL DROP TABLE #tmpSearch
CREATE TABLE #tmpSearch (
    ListIndex int identity(1,1),
    CustomSQL nvarchar(2000)
)
Print 'Getting tables...'
INSERT #tmpSearch (CustomSQL)
select 'IF EXISTS (select * FROM [' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @search + '%'') BEGIN PRINT ''Table ' + TABLE_NAME + ', Column ' + COLUMN_NAME + ''';select * FROM [' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @search + '%'' END'  FROM information_schema.columns
where DATA_TYPE IN ('ntext', 'nvarchar', 'uniqueidentifier', 'char', 'varchar', 'text')
and TABLE_NAME NOT IN ('table_you_dont_want_to_look_in', 'and_another_one') 


Print 'Searching...

'
declare @index int
declare @customsql nvarchar(2000)
WHILE EXISTS (SELECT * FROM #tmpSearch)
BEGIN
    SELECT @index = min(ListIndex) FROM #tmpSearch

    SELECT @customSQL = CustomSQL FROM #tmpSearch WHERE ListIndex = @index

    IF @customSql IS NOT NULL
        EXECUTE (@customSql)

    SET NOCOUNT ON
    DELETE #tmpSearch WHERE ListIndex = @index
    SET NOCOUNT OFF
END

print 'the end.'
ROLLBACK