大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说查找库里面的指定字符串,希望您对编程的造诣更进一步.
–1.定义需要查找的关键字。在搜索中，使用模糊搜索：LIKE “%@key_find%”
DECLARE @key_find NVARCHAR(MAX) = “0761VD0062296731%”;–假设是找字符串”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;
select a.s_SmCode as “电池条码”,
a.s_D as “电池档位”,
a.s_VolC as OCV3,
a.s_ResiC as OCR3,
a.s_TimeC as OCT4,
a.s_VolD as OCV4,
a.s_ResiD as OCR4,
a.s_TimeD as OCT4,
a.s_K34
from tb_bt_15_0633VD0061 a
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/7065.html
