DDR爱好者之家 Design By 杰米
复制代码 代码如下:
USE [数据库名称];
--1.定义需要查找的关键字。在搜索中,使用模糊搜索:LIKE '%@key_find%'
DECLARE @key_find NVARCHAR(MAX) = '123';--假设是找字符串"123"
--2.用游标Cursor_Table,遍历所有表
DECLARE Cursor_Table CURSOR FOR
SELECT name from sysobjects WHERE xtype = 'u' AND name <> 'dtproperties';
OPEN Cursor_Table;
DECLARE @tableName NVARCHAR(MAX);
FETCH NEXT from Cursor_Table INTO @tableName;
WHILE @@fetch_status = 0
BEGIN
DECLARE @tempSQLText NVARCHAR(MAX) = '';
--3.在表中,用游标columnCursor,遍历所有字段。注意,只遍历字符串类型的字段(列)
DECLARE columnCursor CURSOR FOR
SELECT Name FROM SysColumns WHERE ID = Object_Id( @tableName ) and
(
xtype = 35 or --text
xtype = 99 or --ntext
xtype = 167 or --varchar
xtype = 175 or --char
xtype = 231 or --nvarchar
xtype = 239 or --nchar
xtype = 241 --xml
)
OPEN columnCursor;
DECLARE @columnName NVARCHAR(MAX);
FETCH NEXT from columnCursor INTO @columnName;
WHILE @@fetch_status = 0
BEGIN
--4.在表的字段中,对每一行进行模糊搜索,并输出找到的信息。
DECLARE @DynamicSQLText NVARCHAR(MAX) = 'IF ( EXISTS ( SELECT * FROM [' + @tableName + '] WHERE [' + @columnName + '] LIKE ''%' + @key_find + '%'' ) ) BEGIN DECLARE @CurrentTableCount Bigint = ( SELECT COUNT(*) From [' + @tableName + '] ); PRINT ''Find : Table [' + @tableName + '], Column [' + @columnName + '], Row Count:'' + CAST( @CurrentTableCount AS NVARCHAR(MAX) ) + ''.''; END';
EXEC( @DynamicSQLText );
FETCH NEXT from columnCursor INTO @columnName
END
exec(@tempSQLText);
CLOSE columnCursor;
DEALLOCATE columnCursor;
FETCH NEXT from Cursor_Table INTO @tableName;
END
CLOSE Cursor_Table;
DEALLOCATE Cursor_Table;
USE [数据库名称];
--1.定义需要查找的关键字。在搜索中,使用模糊搜索:LIKE '%@key_find%'
DECLARE @key_find NVARCHAR(MAX) = '123';--假设是找字符串"123"
--2.用游标Cursor_Table,遍历所有表
DECLARE Cursor_Table CURSOR FOR
SELECT name from sysobjects WHERE xtype = 'u' AND name <> 'dtproperties';
OPEN Cursor_Table;
DECLARE @tableName NVARCHAR(MAX);
FETCH NEXT from Cursor_Table INTO @tableName;
WHILE @@fetch_status = 0
BEGIN
DECLARE @tempSQLText NVARCHAR(MAX) = '';
--3.在表中,用游标columnCursor,遍历所有字段。注意,只遍历字符串类型的字段(列)
DECLARE columnCursor CURSOR FOR
SELECT Name FROM SysColumns WHERE ID = Object_Id( @tableName ) and
(
xtype = 35 or --text
xtype = 99 or --ntext
xtype = 167 or --varchar
xtype = 175 or --char
xtype = 231 or --nvarchar
xtype = 239 or --nchar
xtype = 241 --xml
)
OPEN columnCursor;
DECLARE @columnName NVARCHAR(MAX);
FETCH NEXT from columnCursor INTO @columnName;
WHILE @@fetch_status = 0
BEGIN
--4.在表的字段中,对每一行进行模糊搜索,并输出找到的信息。
DECLARE @DynamicSQLText NVARCHAR(MAX) = 'IF ( EXISTS ( SELECT * FROM [' + @tableName + '] WHERE [' + @columnName + '] LIKE ''%' + @key_find + '%'' ) ) BEGIN DECLARE @CurrentTableCount Bigint = ( SELECT COUNT(*) From [' + @tableName + '] ); PRINT ''Find : Table [' + @tableName + '], Column [' + @columnName + '], Row Count:'' + CAST( @CurrentTableCount AS NVARCHAR(MAX) ) + ''.''; END';
EXEC( @DynamicSQLText );
FETCH NEXT from columnCursor INTO @columnName
END
exec(@tempSQLText);
CLOSE columnCursor;
DEALLOCATE columnCursor;
FETCH NEXT from Cursor_Table INTO @tableName;
END
CLOSE Cursor_Table;
DEALLOCATE Cursor_Table;
DDR爱好者之家 Design By 杰米
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
DDR爱好者之家 Design By 杰米
暂无评论...
更新日志
2024年06月17日
2024年06月17日
- 江蕙1996-台语畅销全曲珍藏版2CD[台湾][WAV整轨]
- 单依纯《想你时风起》开盘母带[低速原抓WAV+CUE]
- 群星.1990-宝丽金巨星荟萃(引进版)【宝丽金】【WAV+CUE】
- 电影原声大碟《速度与激情4》2009[WAV+CUE]
- 群星《90后经典流行歌曲精选五百首》[320K/MP3][5.4G]
- 《张宇 苦情歌 3CD》[WAV+CUE][2.2GB]
- 刀郎《2020年精选歌曲》[FLAC/分轨][305.6MB]
- 魏如萱2011-不允许哭泣的场合[亚神音乐][WAV+CUE]
- 张敬轩.2005-我的梦想我的路【几何娱乐】【WAV+CUE】
- 林一峰2003-TRAVELOGUE1游乐[香港首版][WAV+CUE]
- 刘德华《1999-2011年演唱会》[MKV][33.4G]
- 邓紫棋《2013年演唱会》[MKV][15.8G]
- 《张杰 无损音乐合集 2005-2023》[FLAC/分轨][11.8GB]
- 【休闲沙发】VA-2024-TranquilHorizons:ChilloutYourMind(FLAC)
- 【休闲沙发】VA-SeaMelodies:ChilloutYourMind(2024)[FLAC]