Search all Tables for a Column Name

Last updated
Save as PDF

Overview

Use SQL statements to search the database for all tables that have columns with a specific phrase in them, then search the columns in those tables for the existence of another phrase.

Details

Run the following code to create a Stored Procedure that can then be called as shown below.

/****** Object: StoredProcedure [dbo].[TP__SearchTablesForColumnNames] Script Date: 07/11/2014 14:02:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TP__SearchTablesForColumnNames]
-- CREATED 7/19/2013 FOR USE WITH SUPPORT CASES
-- SEARCHES ALL TABLES FOR A SPECIFIC COLUMN NAME, THEN IF IT HAS THAT COLUMN NAME, IT SEARCHES FOR THAT VALUE
-- EXAMPLE: TO LOOK FOR ALL TABLES THAT HAVE A STAFFKEY OF 2244 RUN AS FOLLOWS: EXECUTE SUPT_SEARCHDATA 'STAFFKEY', '2244'

@SEARCHCOLUMN VARCHAR(50), -- column name you are searching for
@COLUMNVALUE   VARCHAR(50) -- value in the column
AS
SET NOCOUNT ON
DECLARE @SQL      VARCHAR(8000),
@TABLENAME        VARCHAR(60),
@COLUMNNAME       VARCHAR(60)
--@COLZ             VARCHAR(7000)

CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(7800))

SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
    FROM SYSOBJECTS
      INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
    WHERE SYSOBJECTS.XTYPE='U'
      and syscolumns.name like @SEARCHCOLUMN
    ORDER BY TBLNAME,COLNAME

SELECT * FROM #FKFINDER

BEGIN
      DECLARE C1 CURSOR FOR
        SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME
      OPEN C1
      FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
      WHILE @@FETCH_STATUS <> -1
        BEGIN
          SET @SQL = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @COLUMNVALUE + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''''' + @COLUMNVALUE + ''''' '') ;'
          --PRINT @SQL
          EXEC (@SQL)
        FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
        END
      CLOSE C1
      DEALLOCATE C1
    END

SELECT * FROM #RESULT

Once the Stored Procedure is created, you can call it and send in the appropriate values, such as:

exec TP__SearchTablesForColumnNames '%nam%', '%smith%'

This will find all tables that have a column name with the value 'name' in it (for example FullName) and then create the select statements that search all of these columns in each of the tables for a value like 'smith'.

This can be useful if you need to find all of the places in the database where John Smith's name appears so you know where updates need to be done if there are changes to his name.

Sample output looks like this:

Bil_BatchOriginal Fullname   SELECT * FROM Bil_BatchOriginal WHERE Fullname LIKE '%smith%'
Bil_BatchRebill   Fullname   SELECT * FROM Bil_BatchRebill WHERE Fullname LIKE '%smith%'
BIL_ChargeAging fullname   SELECT * FROM BIL_ChargeAging WHERE fullname LIKE '%smith%'
BIL_HCFA    INS_NAME_L1   SELECT * FROM BIL_HCFA WHERE INS_NAME_L1 LIKE '%smith%'
BIL_HCFA   INS_NAME_L2   SELECT * FROM BIL_HCFA WHERE INS_NAME_L2 LIKE '%smith%'
BIL_HCFA   PT_NAME_F   SELECT * FROM BIL_HCFA WHERE PT_NAME_F LIKE '%smith%'
BIL_HCFA   RP_FOLDER_NAME   SELECT * FROM BIL_HCFA WHERE RP_FOLDER_NAME LIKE '%smith%'
BIL_InsPolicies   NameL   SELECT * FROM BIL_InsPolicies WHERE NameL LIKE '%smith%'
BIL_PriorAuths   ContactNameL   SELECT * FROM BIL_PriorAuths WHERE ContactNameL LIKE '%smith%'
BIL_SpclProc   ClientName   SELECT * FROM BIL_SpclProc WHERE ClientName LIKE '%smith%'
BIL_UB92   Box12Name   SELECT * FROM BIL_UB92 WHERE Box12Name LIKE '%smith%'
BIL_UB92   Box58AInsName   SELECT * FROM BIL_UB92 WHERE Box58AInsName LIKE '%smith%'

TIER^® Tidbits are brought to you weekly by Netsmart TIER Support.

If you have a question about a Tidbit or suggestions on future Tidbits, please contact us at TierSupport@ntst.com.