Geeks With Blogs

News


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

2….Here is another TSQL code which helps in searching all stored procedures:

   1: SELECT Name FROM sys.procedures
   2: WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%ProcessLog%'

OR use (this will search sprocs, udfs, triggers etc. (not just sprocs)):

   1: SELECT object_name(id) FROM syscomments where text like '%ProcessLog%'

3….. Here is another SQL which is helpful. If you have a lot of columns in a table and you need to find columns which are of a particular data type (e.g. datetime) then we can use this:

   1: SELECT * FROM SYSCOLUMNS WHERE Id = OBJECT_ID('geneva_security_extract') AND XTYPE = 61

. you pass in the name of the table to the OBJECT_ID function. Hope this helps

Posted on Friday, June 18, 2010 5:06 AM | Back to top


Comments on this post: Search All Columns in All Tables using TSQL

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Rohit Gupta | Powered by: GeeksWithBlogs.net