[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 628
  • Last Modified:

Search all tables for column name...

I want to use sp_msforeachtable and retrieve table names where 'caudrecno' or 'maudrecno' exists in the table?
0
donnatronious
Asked:
donnatronious
1 Solution
 
sphillips1971Commented:
Not sure if this helps you, but I use this SP in SQL 2000 to accomplish the same thing (proc found elsewhere on this site):

CREATE PROC 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

GO
0
 
dreadyCommented:
how about using Information_Schema.Columns?

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME in ('caudrecno', 'maudrecno' )
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
I would definitely go with the INFORMATION_SCHEMA views so that you can look in one statement.

Ben.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now