Need to dynamically check for columns in other databases

Question

My application runs over several databases, and it needs to be able to check from one to see if a column exists in the other. Unfortunately, I won't know the name of the second database until runtime, so it needs to be dynamic. Also, it has to do this in multiple places, so ideally I'd like to make it into a function, but this gives me problems because functions won't run dynamic SQL.

This is the (non-working) function I wrote.....

CREATE FUNCTION [dbo].[fn_checkcolexists] (
    @dbname VARCHAR(100)
    ,@tablename VARCHAR(100)
    ,@colname VARCHAR(100)
    )
RETURNS BIT
AS
BEGIN
    DECLARE @sqlstring NVARCHAR(2000)

    SET @sqlstring = 'select @retVal = 1 from ' + @dbname + '.sys.columns cols inner join yodata_dev_load.sys.tables tabs
on cols.object_ID=tabs.object_ID where cols.name=''' + @colname + ''' and tabs.name=''' + @tablename + ''''

    DECLARE @retVal INT

    EXEC sp_executesql @sqlstring
        ,N'@retVal int output'
        ,@retVal OUTPUT

    RETURN @retval
END

Has anyone got any suggestions how I can accomplish this? I can't find a way to access the column information for every database. Does this information exist in the system databases anywhere?

Alternatively, can I create some sort of synonym for the other database?

Edit: How to find column names for all tables in all databases in SQL Server isn't an ideal solution, because it also relies on dynamic SQL, so I couldn't use this as a function

this depends on DBMS you are using, for sql server it is ,dbname.INFORMATION_SCHEMA.COLUMNS for columns and dbname.INFORMATION_SCHEMA.TABLES for tables. — Kryptonian, Jul 24 '15 at 11:30
Don't they only work at Database level? I need the list of all columns on all databases — Bakhesh, Jul 24 '15 at 11:32
possible duplicate of [How to find column names for all tables in all databases in SQL Server](http://stackoverflow.com/questions/2729126/how-to-find-column-names-for-all-tables-in-all-databases-in-sql-server). anyway be aware that [dynamic sql is not allowed inside a udf](http://stackoverflow.com/questions/9607935/call-dynamic-sql-from-function) so you'd better look for a different approach (stored procedure or clr comes to mind) — Paolo, Jul 24 '15 at 11:37
Yeah, I did look at that, but as you mention, that would be full of dynamic, so I wouldn't be able to make it into a function — Bakhesh, Jul 24 '15 at 11:40
Why does it have to be a function? is there any reason for it? how do you want to use this function? — CeOnSql, Jul 24 '15 at 11:43
Only because it gets called in multiple places. It would be possible to achieve this with 8-9 lines of code, but I would have to paste those lines in a lot of places, and a function just seemed a neater solution Ideally, it would just be something I could pass the dbname, tablename and column name to, and it would give me a 1 or 0 as to whether it existed or not. At the moment, it's hardcoded as... IF exists (select 1 from dbname.tablename.columnname) but as we get more instances of this application, that is becoming unmanageable. — Bakhesh, Jul 24 '15 at 11:55

score 0 · Answer 1 · answered Jul 24 '15 at 11:43

0

Use stored procedure and use one of these

One of the methods is to use undocumented

EXEC sp_msforeachdb 'SELECT table_catalog FROM ?.INFORMATION_SCHEMA.COLUMNS
where table_name=''your_table'' and column_name=''your_column_name'''

or simulate it

declare @sql varchar(max), @table_name varchar(100)
select @sql='', @table_name='your_table'
select  @sql=@sql+ 'SELECT table_catalog 
FROM '+name+'.INFORMATION_SCHEMA.TABLES 
 where table_name='''+@table_name+'''  and 
 column_name=''your_column_name''' from sys.databases
exec(@sql)

answered Jul 24 '15 at 11:43

Madhivanan

13,470
1
24
29

This solution doesn't seem ideal, because it uses dynamic SQL, so I can't wrap it up in a function. Also, it queries each database in turn, which seems a bit inefficient. I was hoping for a table similar to sys.columns, but that contains information about all db's, but I guess no such thing exists – Bakhesh Jul 24 '15 at 12:03

score 0 · Answer 2 · answered Jul 24 '15 at 13:16

I think I've got the solution I was after. I am using COL_LENGTH, which seems to do the job. You can specify a dbname to is, and even pass that as a parameter, and it returns a null if the column does not exist.

eg

declare @dbname varchar(200)='dbname'

select COL_LENGTH(@dbname + '.dbo.tablename','columnname')

if this returns a null, the column doesn't exist

Many thanks for all the contributors to this thread

score 0 · Answer 3 · answered Jul 24 '15 at 13:33

Hope this works for you

CREATE FUNCTION [dbo].[fn_checkcolexists]
(
    @dbname VARCHAR(100)
    ,@tablename VARCHAR(100)
    ,@colname VARCHAR(100)
)
RETURNS INT
AS
BEGIN
    DECLARE @RECCOUNT INT = 0
    SELECT @RECCOUNT = COUNT(*) FROM   information_schema.columns WHERE  TABLE_CATALOG = @dbname AND COLUMN_NAME = @colname  AND TABLE_NAME = @tablename    
    RETURN @RECCOUNT  
END
GO

Need to dynamically check for columns in other databases

3 Answers3