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] (
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'
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
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 @[email protected]+ 'SELECT table_catalog FROM '+name+'.INFORMATION_SCHEMA.TABLES where table_name='''[email protected]_name+''' and column_name=''your_column_name''' from sys.databases exec(@sql)
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.
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
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