SysColumns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SysColumns

Author

Topic

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-05-23 : 16:56:21

Is it possible to check existence of the object in different database, for instance I'm connected to DB1 and I want to run:

if exists(select * from DB2.dbo.SysColumns
where name = 'MyCol' and ID in
(select ID from DB2.dbo.sysobjects
where (id = object_id(N'[dbo].[MyTable]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)))
begin
print 'yes'
end
else
begin
print 'no'
end

This always returns 'no', even when the object does exist.

Thanks,
Vic

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 17:02:11

[code]
if exists (select * from DB2.dbo.SysColumns where name = 'MyCol' and ID = (Select Id From DB2.dbo.sysobjects Where Name = 'MyTable' and type = 'u'))
begin
print 'yes'
end
else
begin
print 'no'
end
[/code]

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-05-23 : 17:07:18

Thanks, looks like in SQL 2005 we have to use sysobjects.name instead of id
and sysobjects.type instead of OBJECTPROPERTY

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-23 : 17:26:30

The OBJECT_ID function only sees objects in the current database, unless you qualify it with the database name. Even if you qualify it, the OBJECTPROPERTY function can only refer to an object in the current database.

It is better to use the information_schema views.


if exists(
	select *
	from
		MyDB.information_schema.COLUMNS a
		join
		MyDB.information_schema.TABLES b
	on
		a.TABLE_SCHEMA	= b.TABLE_SCHEMA	and
		a.TABLE_NAME	= b.TABLE_NAME		and
		b.TABLE_SCHEMA	= 'dbo'			and
		b.TABLE_NAME	= 'MyTable'		and
		b.TABLE_TYPE	= 'BASE TABLE'
	)
	begin
	print 'yes'
	end
else
	begin
	print 'no'
	end

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources