Post reply

Same table name used in multiple databases question

  • April 21, 2008 at 3:24 pm

    #189581

    My scripts drop and create tables

    Is there exactly ONE sysobjects per SQL Server instance?

    If I have one sql server instance, multiple databases in that instance,

    ( for example: DB_Customer1, DB_Customer2, DB_Customer3 etc...)

    and use the same table names ( for example Products) in each of those databases, how does it discern DB_Customer1.Products from DB_Customer2.Products?

    My SQL Statements do not qualify explicitly. See below:

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[Products]

    GO

    Thanks

  • April 21, 2008 at 4:34 pm

    #805236

    Is there exactly ONE sysobjects per SQL Server instance?

    I believe there is one sysobjects per data base.

    If you run the following query you should receive three different ids but only one record per result set.

    select * from DB_Customer1.dbo.sysobjects where name = 'Products'

    select * from DB_Customer2.dbo.sysobjects where name = 'Products'

    select * from DB_Customer3.dbo.sysobjects where name = 'Products'

  • April 21, 2008 at 9:51 pm

    #805282

    One per database is correct.

  • April 22, 2008 at 8:03 am

    #805543

    Thank you both Travis and Steve. I can move forward.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply