Need a script to check duplicate names in my DB

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)

Need a script to check duplicate names in my DB

Author

Topic

RH.6530
Starting Member

9 Posts

Posted - 2008-01-07 : 15:55:33

Hey, can anyone provide me with a script to check duplicate names with a database with 2005 SQL? I tried one I know for 2000 but it does not work with 2005. Please assist...

Thank you,

pootle_flump

1064 Posts

Posted - 2008-01-07 : 16:05:43

How do you mean duplicate names? Duplicate table names? Column names? Code object names? Or duplicated data?

What is your 2K script - most 2K stuff should work fine in 2005.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-07 : 16:07:16

select name, type from sysobjects
group by name, type
having count(*) > 1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-07 : 17:24:23

The combination of object schema and object name should be unique in a database, unless there is a serious problem in the system tables.

In other words, the following query should alway return zero rows:

select uid, name from sysobjects group by uid, name having count(*) <> 1

CODO ERGO SUM

RH.6530
Starting Member

9 Posts

Posted - 2008-01-10 : 09:19:58

I have a project server environment and the backend SQL databases holds the users accounts created for that environment. User ID can be created two different ways and each having a unique GUID. I need to find out about duplicate user accounts without having to manually search through the list. Here is the 2k script I was using that does not work.

select res_uid, res_name, res_euid from msp_resources where res_name in (select distinct r1.RES_NAME from dbo.MSP_RESOURCES r1 inner join dbo.MSP_RESOURCES r2 on(r1.RES_NAME = r2.RES_NAME and r1.PROJ_ID = r2.PROJ_ID) where r1.PROJ_ID = 1 and r1.RES_UID != r2.RES_UID) and proj_id = 1 order by res_name asc

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-01-10 : 09:44:38

query looks fine, is it giving some error or simply not returning any resultset?

RH.6530
Starting Member

9 Posts

Posted - 2008-01-10 : 09:50:55

This is the error I'm receiving.

Msg 207, Level 16, State 1, Line 5
Invalid column name 'PROJ_ID'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'PROJ_ID'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'PROJ_ID'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'proj_id'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'res_euid'.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-01-10 : 10:08:21

can you post the table creation DDL?

right click on table name

Script Table as > Create to > New Query Editor Window

copy the script produced and paste it here

RH.6530
Starting Member

9 Posts

Posted - 2008-01-10 : 10:26:07

This is the table script for the MSP.resources table

USE [Prod_ProjectServer_Published_Back]
GO
/****** Object: Table [dbo].[MSP_RESOURCES] Script Date: 01/10/2008 09:25:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MSP_RESOURCES](
[RES_UID] [dbo].[UID] NOT NULL,
[RES_ID] [int] IDENTITY(1,1) NOT NULL,
[RES_SECURITY_GUID] [dbo].[UID] NULL DEFAULT (newid()),
[RES_NAME] [dbo].[NAME] NOT NULL,
[WRES_ACCOUNT] [dbo].[NAME] NULL,
[RES_IS_WINDOWS_USER] [dbo].[FLAG] NULL DEFAULT ((0)),
[WRES_EMAIL] [dbo].[NAME] NULL,
[WRES_EMAIL_LANGUAGE] [int] NULL,
[WRES_EMAIL_MODE] [dbo].[ENUM] NULL,
[WRES_IS_OFFLINE] [dbo].[FLAG] NULL,
[WRES_LAST_CONNECT_DATE] [datetime] NULL,
[WRES_PASSWORD] [dbo].[NAME] NULL,
[RES_PHONETICS] [ntext] NULL,
[RESERVED_DATA1] [int] NULL,
[RESERVED_DATA2] [int] NULL,
[RESERVED_DATA3] [int] NULL,
[RESERVED_DATA4] [int] NULL,
[WRES_AD_GUID] [dbo].[UID] NULL,
[RES_PREVENT_ADSYNC] [dbo].[FLAG] NULL DEFAULT ((0)),
[RES_HAS_NOTES] [dbo].[FLAG] NULL DEFAULT ((0)),
[RES_CAN_LEVEL] [bit] NOT NULL DEFAULT ((0)),
[RES_STD_RATE_FMT] [smallint] NOT NULL DEFAULT ((2)),
[RES_OVT_RATE_FMT] [smallint] NOT NULL DEFAULT ((2)),
[RES_ACCRUE_AT] [smallint] NOT NULL DEFAULT ((3)),
[RES_WORKGROUP_MESSAGING] [smallint] NULL,
[RES_AVAIL_FROM] [datetime] NULL,
[RES_AVAIL_TO] [datetime] NULL,
[RES_STD_RATE] [dbo].[RATE] NULL,
[RES_OVT_RATE] [dbo].[RATE] NULL,
[RES_MAX_UNITS] [decimal](25, 6) NULL DEFAULT ((10000)),
[RES_INITIALS] [nvarchar](255) NULL,
[RES_MATERIAL_LABEL] [dbo].[NAME] NULL,
[RES_RTF_NOTES] [image] NULL,
[RES_TYPE] [smallint] NOT NULL DEFAULT ((2)),
[RES_CHECKOUTBY] [dbo].[UID] NULL,
[RES_CHECKOUTDATE] [datetime] NULL,
[RES_BOOKING_TYPE] [dbo].[ENUM] NOT NULL DEFAULT ((0)),
[RES_RATES_TABLE] [image] NULL,
[RES_AVAILABILITY] [image] NULL,
[RES_COST_PER_USE] [dbo].[COST] NULL,
[RES_PCT_WORK_COMPLETE] [dbo].[PCT] NULL,
[RES_IS_NULL] [dbo].[FLAG] NULL DEFAULT ((0)),
[RES_ASSN_START] [datetime] NULL,
[RES_ASSN_FINISH] [datetime] NULL,
[RES_HYPERLINK_FRIENDLY_NAME] [dbo].[NAME] NULL,
[RES_HYPERLINK_ADDRESS] [dbo].[NAME] NULL,
[RES_HYPERLINK_SUB_ADDRESS] [dbo].[NAME] NULL,
[RES_EXTERNAL_ID] [dbo].[NAME] NULL,
[RES_GROUP] [dbo].[NAME] NULL,
[RES_CODE] [dbo].[NAME] NULL,
[RES_HIRE_DATE] [datetime] NULL,
[RES_TERMINATION_DATE] [datetime] NULL,
[RES_DEF_ASSN_OWNER] [dbo].[UID] NULL,
[RES_TIMESHEET_MGR_UID] [dbo].[UID] NULL,
[RES_COST_CENTER] [dbo].[NAME] NULL,
[RES_IS_TEAM] [dbo].[FLAG] NOT NULL DEFAULT ((0)),
[RES_LAST_ACTIVITY] [datetime] NULL,
[CREATED_DATE] [dbo].[CREATED_DATE] NOT NULL DEFAULT (getdate()),
[MOD_DATE] [dbo].[MOD_DATE] NOT NULL DEFAULT (getdate()),
[REV_NEXT_SEED] [int] NOT NULL DEFAULT ((1)),
[REV_RANK] [int] NOT NULL DEFAULT ((0)),
[RES_CURRENT_REV_COUNTER] [int] NOT NULL DEFAULT ((0)),
[RES_CURRENT_REV_RANK] [int] NOT NULL DEFAULT ((0)),
[CREATED_REV_COUNTER] [int] NOT NULL DEFAULT ((0)),
[MOD_REV_COUNTER] [int] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_MSP_RESOURCES] PRIMARY KEY CLUSTERED
(
[RES_UID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

jammus
Starting Member

6 Posts

Posted - 2008-01-10 : 10:29:13

there doesn't seem to be a PROJ_ID field. what is the table you were using previously when it worked?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-01-10 : 10:34:50

looks like the table structure in your 2000 server is different from the one in 2005 server

RH.6530
Starting Member

9 Posts

Posted - 2008-01-10 : 13:30:25

I'm noticing that. Let me do some adjusting of the script. Thanks for pointing me in the right direction.