Find tables w/o primary keys but w/ unique clustered indexes

Hi
I run SQL Server 2000 SP3 on Windows 2000 SP4.
I want to find out, which "user tables" in my DB don't have
a primary key but do have at least one unique index. I
couldn't find any documented way to do this straight away.
So I came up with the following query, which seems to work:
SELECT OBJECT_NAME(id)
FROM sysindexes
WHERE indid > 0 and indid < 255 and (status & 2048) = 0
and (status & 2) <> 0
Does this query seem to be correct and is there any better
(documented) way of doing what I'm trying to do?
--
Thanks,
OskHow about:
SELECT DISTINCT
table_name,
constraint_name
FROM information_schema.table_constraints c
WHERE constraint_type = 'UNIQUE'
AND NOT EXISTS
(SELECT *
FROM information_schema.table_constraints c1
WHERE C1.table_name = c.table_name
and c1.constraint_type = 'PRIMARY KEY')
AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
'IsClustered') = 1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Try,
select distinct
object_name(a.[id])
from
sysindexes as a
left join
(
select
table_name
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
constraint_type = 'PRIMARY KEY'
) as b
on object_name(a.[id]) = b.table_name
where
b.table_name is null
and indexproperty(a.[id], [name], 'IsUnique') = 1
and objectproperty(a.[id], 'IsMSShipped') = 0
go
AMB
"Osk" wrote:
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Thanks!
Shame on me - IDEXPROPERTY was the way to go.
>--Original Message--
>Try,
>select distinct
> object_name(a.[id])
>from
> sysindexes as a
> left join
> (
> select
> table_name
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> constraint_type = 'PRIMARY KEY'
> ) as b
> on object_name(a.[id]) = b.table_name
>where
> b.table_name is null
> and indexproperty(a.[id], [name], 'IsUnique') = 1
> and objectproperty(a.[id], 'IsMSShipped') = 0
>go
>
>AMB
>"Osk" wrote:
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>>
>.
>|||Thanks, Adam.
Still, I wasn't exactly looking for tables with UNIQUE
constraints but rather - with UNIQUE indexes.
>--Original Message--
>How about:
>SELECT DISTINCT
> table_name,
> constraint_name
>FROM information_schema.table_constraints c
>WHERE constraint_type = 'UNIQUE'
> AND NOT EXISTS
> (SELECT *
> FROM information_schema.table_constraints c1
> WHERE C1.table_name = c.table_name
> and c1.constraint_type = 'PRIMARY KEY')
> AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
>'IsClustered') = 1
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.datamanipulation.net
>--
>
>"Osk" <anonymous@.discussions.microsoft.com> wrote in message
>news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>
>.
>|||Lookup the meta data function OBJECTPROPERTY and its various possible values
for the second argument in SQL Server Books Online. You can get the list
using:
SELECT name
FROM sysobjects
WHERE s1.xtype = 'U'
AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
Alternatively, you can also use INFORMATION_SCHEMA views instead of
accessing the system table directly.
--
Anith|||Yes, Anith, thanks!
Though initially I wasn't really interested in finding
tables with UNIQUE constraints but rather with UNIQUE
indexes, which is not the same, is it?
>--Original Message--
>Lookup the meta data function OBJECTPROPERTY and its
various possible values
>for the second argument in SQL Server Books Online. You
can get the list
>using:
>SELECT name
> FROM sysobjects
> WHERE s1.xtype = 'U'
> AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
> AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
>Alternatively, you can also use INFORMATION_SCHEMA views
instead of
>accessing the system table directly.
>--
>Anith
>
>.
>