locked
search some tables from database RRS feed

  • Question

  • User1832207001 posted

    Hello

    I need to create procedure for search all tables exept clients table

    I google it and found this code

    ------------------------------------------------------------------------------------------

    1    alter procedure uspSearchSite --uspSearchSite fdi
    2    @SearchStr nvarchar(100)
    3    as      
    4          
    5          CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
    6    
    7          SET NOCOUNT ON
    8          DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    9    
    10         SET  @TableName = ''
    11         SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
    12   
    13         WHILE @TableName IS NOT NULL
    14         BEGIN
    15               SET @ColumnName = ''
    16   
    17               SET @TableName = 
    18               (
    19                     SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
    20                     FROM INFORMATION_SCHEMA.TABLES
    21                     WHERE       TABLE_TYPE = 'BASE TABLE'
    22                           AND   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    23                           AND   OBJECTPROPERTY(
    24                                       OBJECT_ID(
    25                                             QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
    26                                              ), 'IsMSShipped'
    27                                              ) = 0
    28               )
    29   
    30               WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    31               BEGIN
    32                     SET @ColumnName =
    33                     (
    34                           SELECT MIN(QUOTENAME(COLUMN_NAME))
    35                           FROM INFORMATION_SCHEMA.COLUMNS
    36                           WHERE       TABLE_SCHEMA      = PARSENAME(@TableName, 2)
    37                                 AND   TABLE_NAME  = PARSENAME(@TableName, 1)
    38                                 AND   DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar',  'numeric','decimal', 'double', 'money')
    39                                 AND   QUOTENAME(COLUMN_NAME) > @ColumnName
    40                     )
    41   
    42                     IF @ColumnName IS NOT NULL
    43                     BEGIN
    44                           INSERT INTO #Results
    45                           EXEC
    46                           (
    47                                 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
    48                                 FROM ' + @TableName + ' (NOLOCK) ' +
    49                                 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    50                           )
    51                     END
    52               END   
    53         END
    54   
    55         SELECT ColumnName, ColumnValue FROM #Results
    56   	  
    57   	  DROP TABLE #Results  
    

     

    ------------------------------------------------------------------------------------------

    this code search all table , how can I exept some tables from searching

     

    thanks alot

    Thursday, May 7, 2009 11:08 AM

Answers

  • User1096912014 posted

    Just add

    if UPPER(@TableName) <> 'CLIENTTABLE'

    BEGIN

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    31               BEGIN
    32                     SET
    @ColumnName =
    33                     (
    34                           SELECT MIN(QUOTENAME(COLUMN_NAME)) 35                           FROM INFORMATION_SCHEMA.COLUMNS
    36                           WHERE       TABLE_SCHEMA      = PARSENAME(@TableName, 2)
    37                                 AND   TABLE_NAME  = PARSENAME(@TableName, 1)
    38                                 AND   DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar''numeric','decimal', 'double', 'money')
    39                                 AND   QUOTENAME(COLUMN_NAME) > @ColumnName
    40                     )
    41  
    42                     IF @ColumnName IS NOT NULL
    43                     BEGIN
    44                           INSERT INTO
    #Results
    45                           EXEC
    46                           (
    47                                 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
    48                                 FROM '
    + @TableName + ' (NOLOCK) ' +
    49                                 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    50                           )
    51                     END
    52               END  
    53         END
    END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 7, 2009 11:26 AM

All replies

  • User-501974848 posted

    If you want to exclude some columns then you can add where condition to the output:

    SELECT ColumnName, ColumnValue FROM #Results WHERE ColumnName = 'COLUMN_NAME'

    If you want to get all table name except 'clients' , then you can do the following:

    SELECT * FROM sys.Tables WHERE [name] <> 'clients'

    Thursday, May 7, 2009 11:25 AM
  • User1096912014 posted

    Just add

    if UPPER(@TableName) <> 'CLIENTTABLE'

    BEGIN

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    31               BEGIN
    32                     SET
    @ColumnName =
    33                     (
    34                           SELECT MIN(QUOTENAME(COLUMN_NAME)) 35                           FROM INFORMATION_SCHEMA.COLUMNS
    36                           WHERE       TABLE_SCHEMA      = PARSENAME(@TableName, 2)
    37                                 AND   TABLE_NAME  = PARSENAME(@TableName, 1)
    38                                 AND   DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar''numeric','decimal', 'double', 'money')
    39                                 AND   QUOTENAME(COLUMN_NAME) > @ColumnName
    40                     )
    41  
    42                     IF @ColumnName IS NOT NULL
    43                     BEGIN
    44                           INSERT INTO
    #Results
    45                           EXEC
    46                           (
    47                                 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
    48                                 FROM '
    + @TableName + ' (NOLOCK) ' +
    49                                 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    50                           )
    51                     END
    52               END  
    53         END
    END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 7, 2009 11:26 AM
  • User1832207001 posted

     thanks alot

    I need more than one table , how?

    Thursday, May 7, 2009 11:29 AM
  • User1096912014 posted

    SELECT * FROM sys.Tables WHERE [name] NOT IN ('clients', 'Other')

    Thursday, May 7, 2009 11:53 AM