# --数据查找（值） #
--调用 exec _FindValueInDB '关键字'
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | CREATE PROCEDURE [dbo].[_FindValueInDB] (@value VARCHAR(1024))ASBEGINSET 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 FORSELECT o.name, c.name FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.idWHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239) ORDER BY o.name, c.nameOPEN TABLESFETCH NEXT FROM TABLESINTO @table, @columnWHILE @@FETCH_STATUS = 0BEGINSET @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 TABLESINTO @table, @columnENDCLOSE TABLESDEALLOCATE TABLESSELECT * FROM #tDROP TABLE #tEnd |
# --根据列名找表名 #
1 2 3 | select a.name [表名],b.name [列名]from sysobjects a,syscolumns bwhere a.id=b.id and b.name='关键字' and a.type='U' |
# --统计表数据行数 #
1 2 3 | select t.name as [TableName],i.rows as [RowsCount]from sys.tables as t, sysindexes as iwhere t.object_id = i.id and i.indid <=1 |
