SQL Server - find all tables in database with unique ID value

Question

I want to find out all the tables in the database (in SQLServer) that has the column IDName with particular value 'SAM', like so IDName='SAM'. So my initial apporach was create a table with all the tables that have the column "IDName" (since not all the tables in the database has this column. Then i was thinking of going through each table to see which tables match the IDName='SAM' - This is where i'm stuck. I'm pretty sure there is a lot faster way of doing this too but im not too familiar with database query coding. Anything will help Thanks!

select * into tmp from
(
SELECT SO.NAME AS TableName, SC.NAME AS ColumnName
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
WHERE sc.name = 'IDName'   and SO.type = 'U'
) tablelist

So if I go Select * from tmp, I get the list of tables that have the column "IDName". Now I have to go through each one of that list and see if they have "IDName = 'Sam'" if they do add it to the output table. In the end I want to see all the names of the tables from the database that has the IDName 'Sam'.

If you're happy to use an officially undocumented stored procedure, you can use [`sp_MSforeachtable`](http://stackoverflow.com/questions/9679997/sql-server-sp-msforeachtable-usage-to-select-only-those-tables-which-meet-some-c) which can accept a `@whereand` parameter where you can filter down to just selected tables. — Damien_The_Unbeliever, Oct 17 '14 at 14:53
Sorry im not fimilar with SQL query how would you code that? — civic.sir, Oct 17 '14 at 17:06
@civic.sir - the link takes you to another question/answer which shows how to use it. — Damien_The_Unbeliever, Oct 18 '14 at 07:12

score 4 · Accepted Answer · answered Oct 18 '14 at 15:40

DECLARE @sql AS varchar(max) = '';
DECLARE @ColumnName AS varchar(100) = 'IDName'
DECLARE @ResultQuery AS varchar(max) =  'SELECT ''@TableName'' AS TableName ' + 
                                        '       ,@ColumnName ' +
                                        'FROM @TableName ' +
                                        'WHERE @ColumnName = ''SAM''';
SET @ResultQuery = REPLACE(@ResultQuery, '@ColumnName', QUOTENAME(@ColumnName));

WITH AllTables AS (
    SELECT SCHEMA_NAME(Tables.schema_id) AS SchemaName
          ,Tables.name AS TableName
          ,Columns.name AS ColumnName
    FROM sys.tables AS Tables
        INNER JOIN sys.columns AS Columns 
            ON Tables.object_id = Columns.object_id
    WHERE Columns.name = @ColumnName
)
SELECT @sql = @sql + ' UNION ALL ' +
       REPLACE(@ResultQuery, '@TableName', QUOTENAME(TableName)) + CHAR(13)
FROM AllTables

SET @sql = STUFF(@sql, 1, LEN(' UNION ALL'), '');

--PRINT @sql;

SET @sql = 
'WITH AllTables AS ( ' +
   @sql + 
') ' +
'SELECT DISTINCT TableName ' +
'FROM AllTables ';

EXEC (@sql)

Great Answer, but it will fail if you have schema other than dbo, better to change the replacement line with this: REPLACE(@ResultQuery, '@TableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)) + CHAR(13) — YazX, Apr 06 '20 at 16:12

SQL Server - find all tables in database with unique ID value

1 Answers1