Retrieving which database(s) and tables contains a spesific column name within a multiple database soloution

Question

I wish do retrieve the database which contains table x, based on a column name I enter through e.g. My WHERE statement.

As of now, I run two seperate SELECT queries. Firstly, I search for which tables in the soloution contains a spesific column. Second, I have to manually search for all the resulting databases in the subquery.

I wish to have this dynamic, so that when entering the column name, both database and table are returned. Now, I get "NULL" the Database column.

I've managed to get the current db using only db_name, but that is not what I intend to do..

db_name(db_id(table1.name)) AS "Database" , table1.name AS 'Table', column1.name  AS 'Column'

    FROM sys.columns column1
    JOIN sys.tables table1 ON column1.object_id = table1.object_id
        WHERE column1.name LIKE 'columnname'

    ORDER BY "Table", "Column"

(SELECT "db" FROM sys.databases WHERE CASE WHEN state_desc = 'ONLINE' THEN
        OBJECT_ID(QUOTENAME("db") + '.[dbo].' + '[database1]', 'U')
        END IS NOT NULL)

The code above is working wiithout errors. However, I do not manage to pull the Database name, and I can not understand how I could solve this.

I've used several earlier posts as reference to build up this code, as I'm a rookie to SQL.. :-)

Thanks in advance for any assitance. Br.

I think this was already answered here: https://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name-ms-sql-server — Adrian, May 15 '19 at 13:06
@Adrian the answers don't seem to include multiple database search (except for the 3rd party tools). — Luis Cazares, May 15 '19 at 13:09
@LuisCazares they do. Search in the web page for `sp_MSForEachDB` — Panagiotis Kanavos, May 15 '19 at 13:12
Possible duplicate of [How to find column names for all tables in all databases in SQL Server](https://stackoverflow.com/questions/2729126/how-to-find-column-names-for-all-tables-in-all-databases-in-sql-server) — Panagiotis Kanavos, May 15 '19 at 13:14
@Adrian The second to last least voted answer? That's unlikely to be found. The new suggestion of duplicate is more likely to help. I would avoid all suggestions of sp_MSForEachDB as it's known to skip databases. — Luis Cazares, May 15 '19 at 13:17
Redgate's SQL Search is a free plugin for SSMS and makes this kind of thing trivial to accomplish. — Eric Brandt, May 15 '19 at 13:44

score 0 · Accepted Answer · answered May 15 '19 at 13:07

This gives a bit more information than you requested, but it's a code that I had to create a data dictionary of my databases. You just need to change the value for the first variable to make it work.

--Change this value
DECLARE @ColumnName sysname = 'YourColumnName';

IF OBJECT_ID( 'tempdb..#DataDictionary') IS NOT NULL
    DROP TABLE #DataDictionary;

CREATE TABLE #DataDictionary(
    TABLE_CATALOG       sysname,
    TABLE_SCHEMA        sysname,
    TABLE_NAME          sysname,
    ORDINAL_POSITION    int,
    COLUMN_NAME         sysname,
    DATA_TYPE           sysname,
    IS_NULLABLE         varchar(8)
);

DECLARE @SQL NVARCHAR(MAX);

DECLARE dbs CURSOR LOCAL FAST_FORWARD
FOR
SELECT REPLACE( 'USE <<database_name>>;
INSERT INTO #DataDictionary
SELECT DB_NAME() AS TABLE_CATALOG,
    s.name AS TABLE_SCHEMA,
    t.name AS TABLE_NAME,
    COLUMNPROPERTY(c.object_id, c.name, ''ordinal'')  AS ORDINAL_POSITION,  
    c.name AS COLUMN_NAME,  
    CASE WHEN ty.name IN (''char'', ''varchar'', ''varbinary'', ''binary'') THEN CONCAT( ty.name, ''('', ISNULL( CAST(NULLIF(c.max_length, -1) AS varchar(4)), ''MAX''), '')'')
        WHEN ty.name IN (''nchar'', ''nvarchar'') THEN CONCAT( ty.name, ''('', ISNULL( CAST(NULLIF(c.max_length, -1)/2 AS varchar(4)), ''MAX''), '')'')
        WHEN ty.name IN (''numeric'', ''decimal'') THEN CONCAT( ty.name, ''('', c.precision, '','', c.scale, '')'')
        ELSE ty.name END AS DATA_TYPE,
    IIF(c.is_nullable = 1, ''NULL'', ''NOT NULL'')  AS IS_NULLABLE
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE t.object_id NOT IN( SELECT major_id FROM sys.extended_properties WHERE minor_id = 0 AND class = 1 AND name = N''microsoft_database_tools_support'')
AND c.name = @ColumnName;', '<<database_name>>', name)
FROM sys.databases
WHERE database_id > 4 --No system databases
AND HAS_DBACCESS( name) = 1
AND state_desc = 'ONLINE'
OPEN dbs;

FETCH NEXT FROM dbs INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN 
    EXEC sp_executesql @SQL, N'@ColumnName sysname', @ColumnName;

    FETCH NEXT FROM dbs INTO @SQL;
END;

CLOSE dbs;
DEALLOCATE dbs;

SELECT *
FROM #DataDictionary
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

To get all the columns, just remove the column name comparison in the dynamic code AND c.name = @ColumnName

Retrieving which database(s) and tables contains a spesific column name within a multiple database soloution

1 Answers1