问题描述:

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

网友答案:

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.

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

网友答案:

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
相关阅读:
Top