3

I use SQL Server 2016.

I need to find table name and column name across all databases on a server. 

DECLARE @SQL NVARCHAR(max)

SET @SQL = STUFF((
            SELECT '
UNION
SELECT ' + quotename(NAME, '''') + ' as Db_Name, t.Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name,
c.Name as Column_Name
FROM ' + quotename(NAME) + '.sys.tables as t 
INNER JOIN ' + quotename(NAME) + '.sys.columns as c ON t.object_id = c.object_id
WHERE t.NAME LIKE ''%'' + @TableName + ''%'' 
AND c.NAME LIKE ''%'' + @ColumnName + ''%'' '  
            FROM sys.databases
            ORDER BY NAME
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 8, '')

--PRINT @SQL;

EXECUTE sp_executeSQL @SQL
    ,N'@TableName varchar(30), @ColumnName varchar(30)'
    ,@TableName = 'Bid'
    ,@ColumnName = 'bidid'

I'm getting this error

Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.

Following this thread:

Cannot resolve the collation conflict?

I tried setting collate to 'DATABASE_DEFAULT' by adding to my inner join or where clause, but that did not resolve the error.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Data Engineer
  • 795
  • 16
  • 41
  • 1
    You'll need to use the `COLLATE` clause on every one of your columns, not just one. `C.[Name]` will be effected by the collation issue, as well as `t.[Name]` – Thom A Apr 19 '18 at 16:01
  • 2
    (1) apply the same explicit `collate` clause to the database name that you're already doing with the table and column names; (2) can't you use UNION ALL here? – Aaron Bertrand Apr 19 '18 at 16:01
  • Awesome tips Larnu and Aaron! One of you can post this as an answer so I can except it! By adding to the select list collation as follows: ",c.Name collate SQL_Latin1_General_CP1_CI_AS as Column_Name" it worked perfectly well. – Data Engineer Apr 19 '18 at 18:00
  • There are scripts available already. You are trying to reinvent the wheel here. https://stackoverflow.com/questions/2729126/how-to-find-column-names-for-all-tables-in-all-databases-in-sql-server#2729160 – Venkataraman R Apr 23 '18 at 04:25

1 Answers1

4

By adding to the select list collation as follows: ",c.Name collate SQL_Latin1_General_CP1_CI_AS as Column_Name" it worked perfectly well

Data Engineer
  • 795
  • 16
  • 41