Checking a specific schema for a table

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

General SQL Server Forums

New to SQL Server Programming

Checking a specific schema for a table

Author

Topic

Just_Jeff
Starting Member

7 Posts

Posted - 2011-11-08 : 15:56:40

I've inherited a database that has 2 sets of nearly identical tables. The primary set is assigned to the 'dbo' schema. There is a second set of tables that are almost identical to the primary set but they are assigned to the 'history' schema.

For instance I have 2 tables named tblCompany.
dbo.tblCompany
history.tblCompany

How do I identify the correct table to drop with a statement like the following:

IF EXISTS(SELECT * FROM sys.objects WHERE type='U' AND name='tblCompany')
DROP TABLE history.tblCompany
GO

The select statement returns 2 records, one from each schema. I want to check for the existence of a table from a specific schema. How do I do this?

Thanks,
Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-08 : 16:14:43

SELECT * FROM INFORMATION_SCHEMA.TABLES

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Just_Jeff
Starting Member

7 Posts

Posted - 2011-11-08 : 16:21:53

Thanks. I think I found another way to do it as well.

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[history].[tblCompany]') AND type in (N'U')

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources