sqlserver中在指定数据库的所有表的所有列中搜索给定的值

比如:我们导入了某个客户的资料,我们知道此客户的姓名是ZhangShan,我们想知道,在我们的业务数据库(eg:NorthWind)中,有哪些数据表的哪些字段设置了此姓名值ZhangShan,通过下面的SQL,我们就可以实现此目的,此处的SQL搜索自网上,在此处做了局部修改。
一、搜索数据是String类型
适用于搜索Text,NText,Varchar,Nvarchar,Char,NChar等类型
1、创建存储过程:My_Search_StringInGivenTable 复制代码 代码如下: USE [NORTHWIND] GO
/****** Object: StoredProcedure
[dbo].[My_Search_StringInGivenTable] Script Date: 09/25/2011
15:37:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
GO CREATE PROCEDURE [dbo].[My_Search_StringInGivenTable]
(@SearchString NVARCHAR(MAX), @Table_Schema sysname, @Table_Name
sysname) AS BEGIN DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX),
@PkColumn NVARCHAR(MAX) — Get all character columns SET @Columns =
STUFF((SELECT ‘, ‘ + QUOTENAME(Column_Name) FROM
INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN
(‘text’,’ntext’,’varchar’,’nvarchar’,’char’,’nchar’) AND TABLE_NAME =
@Table_Name ORDER BY COLUMN_NAME FOR XML PATH(”)),1,2,”) IF @Columns
IS NULL — no character columns RETURN -1 — Get columns for select
statement – we need to convert all columns to nvarchar(max) SET @Cols =
STUFF((SELECT ‘, cast(‘ + QUOTENAME(Column_Name) + ‘ as nvarchar(max))
as ‘ + QUOTENAME(Column_Name) FROM INFORMATION_SCHEMA.COLUMNS WHERE
DATA_TYPE IN (‘text’,’ntext’,’varchar’,’nvarchar’,’char’,’nchar’) AND
TABLE_NAME = @Table_Name ORDER BY COLUMN_NAME FOR XML
PATH(”)),1,2,”) SET @PkColumn = STUFF((SELECT N’ + ”|” + ‘ + ‘
cast(‘ + QUOTENAME(CU.COLUMN_NAME) + ‘ as nvarchar(max))’ FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME =
CU.TABLE_NAME AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA AND
Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE
=’PRIMARY KEY’ AND TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME
= @Table_Name ORDER BY CU.COLUMN_NAME FOR XML PATH(”)),1,9,”) IF
@PkColumn IS NULL SELECT @PkColumn = ‘cast(NULL as nvarchar(max))’ —
set select statement using dynamic UNPIVOT DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ‘select *, ‘ + QUOTENAME(@Table_Schema,””) + ‘as [Table
Schema], ‘ + QUOTENAME(@Table_Name,””) + ‘ as [Table Name]’ + ‘
from (select ‘+ @PkColumn + ‘ as [PK Column], ‘ + @Cols + ‘ from ‘ +
QUOTENAME(@Table_Name) + ‘ )src UNPIVOT ([Column Value] for [Column
Name] IN (‘ + @Columns + ‘)) unpvt WHERE [Column Value] LIKE ”%” +
@SearchString + ”%”’ –print @SQL EXECUTE sp_ExecuteSQL @SQL,
N’@SearchString nvarchar(max)’, @SearchString END
2、创建搜索存储过程:My_Search_String_AllTables
此存储过程将遍历指定数据库的所有表,并利用上面创建的存储过程My_Search_StringInGivenTable来取得每个表的搜索结果。
复制代码 代码如下: USE [NORTHWIND] GO
/****** Object: StoredProcedure
[dbo].[My_Search_String_AllTables] Script Date: 09/25/2011
15:41:58 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF
GO CREATE PROC [dbo].[My_Search_String_AllTables] ( @SearchString
NVARCHAR(MAX) ) AS BEGIN CREATE TABLE #RESULT ([PK COLUMN]
NVARCHAR(MAX), [COLUMN VALUE] NVARCHAR(MAX), [COLUMN Name] sysname,
[TABLE SCHEMA] sysname, [TABLE Name] sysname) DECLARE @Table_Name
sysname, @Table_Schema sysname DECLARE curAllTables CURSOR LOCAL
FORWARD_ONLY STATIC READ_ONLY FOR SELECT Table_Schema, Table_Name
FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ‘BASE TABLE’ ORDER
BY Table_Schema, Table_Name OPEN curAllTables FETCH curAllTables INTO
@Table_Schema, @Table_Name WHILE (@@FETCH_STATUS = 0) — Loop through
all tables in the database BEGIN INSERT #RESULT EXECUTE
My_Search_StringInGivenTable @SearchString, @Table_Schema,
@Table_Name FETCH curAllTables INTO @Table_Schema, @Table_Name END —
while CLOSE curAllTables DEALLOCATE curAllTables — Return results
SELECT * FROM #RESULT ORDER BY [Table Name] END 使用示例 复制代码 代码如下: USE [NORTHWIND] GO DECLARE
@return_value int EXEC @return_value =
[dbo].[My_Search_String_AllTables] @SearchString = N’WantValue’
SELECT ‘Return Value’ = @return_value GO
还有另一个版本,就是直接创建一个存储过程来取得所要结果,但个人觉得前面那个方法更具灵活性
复制代码 代码如下: USE [NORTHWIND] GO
/****** Object: StoredProcedure [dbo].[ZL_SearchAllTables]
Script Date: 09/25/2011 15:44:10 ******/ SET ANSI_NULLS OFF GO
SET QUOTED_IDENTIFIER OFF GO CREATE PROC
[dbo].[ZL_SearchAllTables] ( @SearchStr nvarchar(100) ) AS BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256),
@ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = ”
SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””) WHILE
@TableName IS NOT NULL BEGIN SET @ColumnName = ” SET @TableName = (
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)) FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ AND
QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) @TableName AND
OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + ‘.’ +
QUOTENAME(TABLE_NAME) ), ‘IsMSShipped’ ) = 0 ) WHILE (@TableName IS NOT
NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT
MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME =
PARSENAME(@TableName, 1) AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’,
‘nvarchar’) AND QUOTENAME(COLUMN_NAME) @ColumnName ) IF @ColumnName IS
NOT NULL BEGIN INSERT INTO #Results EXEC ( ‘SELECT ”’ + @TableName +
‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630) FROM ‘ +
@TableName + ‘ (NOLOCK) ‘ + ‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ +
@SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results
END [code] 二、搜索数据是Int类型 适用于搜索smallint, tinyint, int,
bigint等类型 1、创建存储过程 My_Search_IntInGivenTable [code] USE
[NORTHWIND] GO /****** Object: StoredProcedure
[dbo].[My_Search_IntInGivenTable] Script Date: 09/25/2011 15:45:46
******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE
PROCEDURE [dbo].[My_Search_IntInGivenTable] (@SearchValue INT,
@Table_Schema sysname, @Table_Name sysname) AS BEGIN DECLARE @Columns
NVARCHAR(MAX) , @Cols NVARCHAR(MAX) , @PkColumn NVARCHAR(MAX) , @SQL
NVARCHAR(MAX) –判断并创建#Result表 IF OBJECT_ID(‘TempDB..#Result’,
‘U’) IS NOT NULL DROP TABLE #Result CREATE TABLE #RESULT ( [PK
COLUMN] NVARCHAR(MAX) , [COLUMN VALUE] BIGINT , [COLUMN Name]
SYSNAME , [TABLE SCHEMA] SYSNAME , [TABLE Name] SYSNAME )
–开始搜索给定的表 DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY
STATIC READ_ONLY FOR SELECT Table_Schema , Table_Name FROM
INFORMATION_SCHEMA.Tables WHERE Table_Name =@Table_Name OPEN
curAllTables WHILE 1 = 1 BEGIN FETCH curAllTables INTO @Table_Schema,
@Table_Name IF @@FETCH_STATUS 0 — Loop through all tables in the
database BREAK PRINT CHAR(13) + ‘Processing ‘ +
QUOTENAME(@Table_Schema) + ‘.’ + QUOTENAME(@Table_Name) — Get all int
columns SET @Columns = STUFF(( SELECT ‘, ‘ + QUOTENAME(Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE LIKE ‘%int’ AND
TABLE_NAME = @Table_Name AND table_schema = @Table_Schema ORDER BY
COLUMN_NAME FOR XML PATH(”) ), 1, 2, ”) IF @Columns IS NULL BEGIN
PRINT ‘No int columns in the ‘ + QUOTENAME(@Table_Schema) + ‘.’ +
QUOTENAME(@Table_Name) CONTINUE END — Get columns for select statement

在itpub中看到一个c# Winform实现对数据库的操作,可惜很多是写死了的,不过这到让我想到了是否可以通过存储过程实现对数据库和表结构的操作。有些时候,项目中可能要动态的增加表字段,删除表字段,重命名之类的,而客户端不一定能访问到远程的服务器,在这个时候也许就会有些许用处了。总结了一下,我把相关的存储过程实现,经在SQL2005 测试是OK的,我把他放上来,跟大家分享,同时欢迎大家多多指教.
希望能对大家有用.

  • we need to convert all columns to bigint SET @Cols = STUFF(( SELECT ‘,
    cast(‘ + QUOTENAME(Column_Name) + ‘ as bigint) as ‘ +
    QUOTENAME(Column_Name) FROM INFORMATION_SCHEMA.COLUMNS WHERE
    DATA_TYPE LIKE ‘%int’ AND TABLE_NAME = @Table_Name ORDER BY
    COLUMN_NAME FOR XML PATH(”) ), 1, 2, ”) — Create PK column(s) SET
    @PkColumn = STUFF(( SELECT N’ + ”|” + ‘ + ‘ cast(‘ +
    QUOTENAME(CU.COLUMN_NAME) + ‘ as nvarchar(max))’ FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME =
    CU.TABLE_NAME AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA AND
    Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE =
    ‘PRIMARY KEY’ AND TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME =
    @Table_Name ORDER BY CU.COLUMN_NAME FOR XML PATH(”) ), 1, 9, ”) IF
    @PkColumn IS NULL SELECT @PkColumn = ‘cast(NULL as nvarchar(max))’ —
    set select statement using dynamic UNPIVOT SET @SQL = ‘select *, ‘ +
    QUOTENAME(@Table_Schema, ””) + ‘as [Table Schema], ‘ +
    QUOTENAME(@Table_Name, ””) + ‘ as [Table Name]’ + ‘ from (select ‘
  • @PkColumn + ‘ as [PK Column], ‘ + @Cols + ‘ from ‘ +
    QUOTENAME(@Table_Schema) + ‘.’ + QUOTENAME(@Table_Name) + ‘ )src
    UNPIVOT ([Column Value] for [Column Name] IN (‘ + @Columns + ‘))
    unpvt WHERE [Column Value] = @SearchValue’ –print @SQL — if we get
    errors, we may want to print generated SQL INSERT #RESULT ( [PK
    COLUMN] , [COLUMN VALUE] , [COLUMN Name] , [TABLE SCHEMA] ,
    [TABLE Name] ) EXECUTE sp_ExecuteSQL @SQL, N’@SearchValue int’,
    @SearchValue PRINT ‘Found ‘ + CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘
    records in ‘ + QUOTENAME(@Table_Schema) + ‘.’ + QUOTENAME(@Table_Name)
    END CLOSE curAllTables DEALLOCATE curAllTables SELECT * FROM #RESULT
    ORDER BY [TABLE SCHEMA] , [TABLE Name] END
    2、创建搜索存储过程My_Search_Int_AllTables,与上面类似,此存储过程将调用
    My_Search_IntInGivenTable来实现所遍历的每一个数据表的搜索结果 复制代码 代码如下: USE [NORTHWIND] GO
    /****** Object: StoredProcedure
    [dbo].[My_Search_Int_AllTables] Script Date: 09/25/2011 15:48:29
    ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
    CREATE PROC [dbo].[My_Search_Int_AllTables] ( @SearchValue INT )
    AS BEGIN CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX), [COLUMN
    VALUE] NVARCHAR(MAX), [COLUMN Name] sysname, [TABLE SCHEMA]
    sysname, [TABLE Name] sysname) DECLARE @Table_Name sysname,
    @Table_Schema sysname DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY
    STATIC READ_ONLY FOR SELECT Table_Schema, Table_Name FROM
    INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ‘BASE TABLE’ ORDER BY
    Table_Schema, Table_Name OPEN curAllTables FETCH curAllTables INTO
    @Table_Schema, @Table_Name WHILE (@@FETCH_STATUS = 0) — Loop through
    all tables in the database BEGIN INSERT #RESULT EXECUTE
    My_Search_StringInGivenTable @SearchValue, @Table_Schema,
    @Table_Name FETCH curAllTables INTO @Table_Schema, @Table_Name END —
    while CLOSE curAllTables DEALLOCATE curAllTables — Return results
    SELECT * FROM #RESULT ORDER BY [Table Name] END 使用示例 复制代码 代码如下: USE [NORTHWIND] GO DECLARE
    @return_value int EXEC @return_value =
    [dbo].[My_Search_Int_AllTables] @SearchValue = 68 SELECT ‘Return
    Value’ = @return_value GO Note:
    1、你可以根据上面一、二中的第1个存储过程来实现只搜索指定某些数据表的功能。
    2、对于其它数据类型如:Date,Real等等均可以此为参照进行修改。
    3、此方法对大型数据库会很耗时,所以尽量在小数据库上调试。当需要在大数据库上操作时,尽量避开数据库使用高峰时段并要有耐心。

创建表

CREATE PROCEDURE dbo.CreateUserTable
    @TableName sysname
AS
    EXEC(‘CREATE TABLE ‘+@TableName+
        ‘ (column1 varchar(100), column2 varchar(100))’);

GO

读取表中字段

create procedure SelectTableField
@TableName varchar(50)
as
SELECT     TABLE_CATALOG AS [Database], TABLE_SCHEMA AS Owner, TABLE_NAME AS TableName, COLUMN_NAME AS ColumnName,  ORDINAL_POSITION AS OrdinalPosition, COLUMN_DEFAULT AS DefaultSetting, IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType,  CHARACTER_MAXIMUM_LENGTH AS MaxLength, DATETIME_PRECISION AS DatePrecision,COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, ‘IsIdentity’) as IsIdentity 
FROM         INFORMATION_SCHEMA.COLUMNS WHERE     (TABLE_NAME = @TableName)

删除表

CREATE PROCEDURE dbo.DropUserTable
    @TableName sysname
AS
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = ‘dbo’
        AND TABLE_TYPE = ‘BASE TABLE’
        AND TABLE_NAME = @TableName)
    BEGIN
      
    EXEC(‘drop TABLE ‘ +  @TableName);
      
    END

表的重命名

CREATE PROCEDURE dbo.ReNameUserTable
    @OldTableName sysname,
    @NewTableName sysname
AS
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = ‘dbo’
        AND TABLE_TYPE = ‘BASE TABLE’
        AND TABLE_NAME = @OldTableName)
    BEGIN
      
    exec sp_rename  @OldTableName ,@NewTableName
      
    END

增加表中字段

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签:
网站地图xml地图