SQL SERVER OBJECT_ID

Question

i want to execute the query in master database it self

IF OBJECT_ID(N'DB1.dbo.T_table1', N'U') IS NOT NULL 
    AND COLUMNPROPERTY(OBJECT_ID(N'db1.dbo.T_table1'), 
                       'KEY_value','AllowsNull') IS NOT NULL 
SELECT 'TRUE' AS COLUMNSTATUS 
ELSE 
SELECT 'FALSE' AS COLUMNSTATUS

but its not working? give me suggestion.

Answer 1

You're checking for NULL, but COLUMPROPERTY will return 1 or 0, if the field exists.

Try:

SELECT CASE COLUMNPROPERTY(OBJECT_ID(N'db1.dbo.T_table1'),
           'KEY_value','AllowsNull') 
        WHEN 0 THEN 'Column exists and does not allow nulls'
        WHEN 1 THEN 'Column exists and allows nulls'
        ELSE 'Column not found'
    END

Answer 2

When testing from master, i get the NULL as expected from

COLUMNPROPERTY(OBJECT_ID(N'db1.dbo.T_table1'),'KEY_value','AllowsNull'),

But when running this on the database to be tested, it returns the 1/0 as expected.

It seems that COLUMNPROPERTY runs on the db being executed.

EDIT:

Try this from master

DECLARE @RetVal TABLE(
     Val INT
)

INSERT INTO @RetVal (Val) EXECUTE ('USE DB1; SELECT COLUMNPROPERTY(OBJECT_ID(N''DB1.dbo.T_table1''),''Key_Value'',''AllowsNull'')')

SELECT TOP 1 * FROM @RetVal