Check all tables in db for value

Question

I have read-only access to four SQL Server databases.

In each database roughly half of the tables have a column named [DIM_1].

I am interested in the number of times a particular string value (e.g. 'ABCD') appears in each of these columns.

I need a way to return a data table that looks like

DatabaseName, TableName, CountOfABCD

I tried initially to do this with a loop, but it was pretty horrible and I ran into the issue that you can't use a variable for a table name.

I'm wondering if there is anything I can do with sp_MSforeachtable but my database skills are falling a little short.

Would anyone have any suggestions? I've found scripts to create stored procedures that perform the task (e.g. http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm ), but I can't create them because of my access level. They also use temp tables, which I also can't create.

Any thoughts gratefully received.

Thanks,

Tom

can you perform select statements on sys.columns, or Information_schema.Columns? — Zohar Peled, Jun 26 '14 at 12:16

score 0 · Accepted Answer · edited May 23 '17 at 11:49

As you said, you can use sp_MSforeachtable, but in your case, I don't know if you will be able to give result in table, because you can't create any.

Here I go through all tables who have requested column.

DECLARE @CountOfABCD AS VARCHAR(20)
SET @CountOfABCD='ABCD'

DECLARE @sql nvarchar(2000)
SET @sql = '
    DECLARE @COUNT AS INT
    SELECT @COUNT=COUNT(*) FROM [SERVER].[DATABASE].? WHERE DIM_1='''+@CountOfABCD+'''
    IF @COUNT>0
    BEGIN
        PRINT PARSENAME("?",2)+''.'' + PARSENAME("?",1)+'' => ''+CONVERT(VARCHAR,@COUNT)+'' ROW(S)''
    END
'
EXEC sp_MSforeachtable @command1=@sql,@whereand='AND O.ID IN (SELECT OBJECT_ID FROM [SERVER].[DATABASE].SYS.COLUMNS C WHERE C.NAME=''DIM_1'')'

If you will be able create table you can change Print to Insert.

Also, if you want run this on several servers just create several copies and change database and server.

I hope this help at least a bit. I found this answer here https://stackoverflow.com/a/9680217/1692632

Thanks. I'm getting this message: Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value 'ABCD' to data type smallint. — Cleland, Jun 26 '14 at 14:52

Check all tables in db for value

1 Answers1