none
Duplicate tables in sql server

    Question

  • i have sql server 2005 environment and have arounds 10-12 DBs.
    i have scramble data and duplicacy issue in terms of tables. when i talk about table m talking about table with the same name.

    there are tables with same name exists on server in multiple database. some having two duplicates, some having 3, 4, 5 and more...
    Also till now this server was working on God's will and i have just transitioned.

    i need few help as i work as DBA not developer so what can be done from DBA aspects.

    1. any tool or way which can let me know which table is primary n required as modify date of table is also not helpful.
    2. how can i move to know which DB table is needed on prod. and rest i can rename for some time before removing.
    3. as server also lacks refertial integrity, is there any automated tool or process which can suggest me possible relationship.


    any help would be appreciated.




    • Edited by skc_chat Tuesday, September 02, 2014 11:54 AM
    Tuesday, September 02, 2014 7:31 AM

All replies

  • Hello,

    I don't understand what you are talking about? Fully qualified object names are unique, you can not have several tables with the same name in a database. You may have in different schema tables with equal names. And no one of use can tell you which ones are needed for test or for productive environment.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 02, 2014 3:37 PM
  • The table names are unique. The tables are belongs to different schema. You can execute the below SQL to get the output and you can make out the difference.

    SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
    AS SchemaTable
    FROM sys.tables

    Query to check the create_date and modify_date of the table

    SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
    AS SchemaTable,create_date,modify_date
    FROM sys.tables

    I will not recommend you to rename the objects until you know what you are doing.

    You can generate the database diagram and analyze the table which requires references.

    --Prashanth

    • Proposed as answer by Naomi NModerator Tuesday, September 02, 2014 4:33 PM
    • Unproposed as answer by skc_chat Wednesday, September 03, 2014 10:32 AM
    Tuesday, September 02, 2014 3:57 PM
  • there are tables with same name exists on server in multiple database

    Whats the issue with that? Its perfectly normal to have tables with same name in multiple dbs

    I didnt understand why you're concerned with it

    1 and 2 you alone can determine as we dont know anything about your system and also on the tables

    3 you can use ALTER TABLE ADD CONSTRAINT statement to add foreign keys to create the relationships


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Tuesday, September 02, 2014 4:52 PM
    Tuesday, September 02, 2014 4:29 PM
  • Hello,

    I don't understand what you are talking about? Fully qualified object names are unique, you can not have several tables with the same name in a database. You may have in different schema tables with equal names. And no one of use can tell you which ones are needed for test or for productive environment.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    tables are in different DB's with same name in production and are confusive which one is getting used as modify date also belong to somewhat same... I also dnt understand the system working as DBA so is there any other way round I can help dev teams to prepare cheat sheet with table which they can focus.
    Wednesday, September 03, 2014 7:16 AM
  • The table names are unique. The tables are belongs to different schema. You can execute the below SQL to get the output and you can make out the difference.

    SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
    AS SchemaTable
    FROM sys.tables

    Query to check the create_date and modify_date of the table

    SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
    AS SchemaTable,create_date,modify_date
    FROM sys.tables

    I will not recommend you to rename the objects until you know what you are doing.

    You can generate the database diagram and analyze the table which requires references.

    --Prashanth

    thanks prasanth, concern is not how to add and within schema. tables are having multiple occurrence.

    more to describe. table name is ABC and it exists on Database X, Y and Z all three while only one DB has real copy and its not the case of few tables so need some way so I can help my dev team.

    Wednesday, September 03, 2014 7:18 AM
  • there are tables with same name exists on server in multiple database

    Whats the issue with that? Its perfectly normal to have tables with same name in multiple dbs

    I didnt understand why you're concerned with it

    1 and 2 you alone can determine as we dont know anything about your system and also on the tables

    3 you can use ALTER TABLE ADD CONSTRAINT statement to add foreign keys to create the relationships


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    issue is you dnt know if you insert a record from website you don't know which DB table would get populated.

    system required restructuring so I was hoping if there is any way we can determine tables which are required and constraint it can suggests.

    Wednesday, September 03, 2014 7:20 AM