Finding unique indexes - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Finding unique indexes

Author

Topic

blakmk
Starting Member

45 Posts

Posted - 2003-09-02 : 09:33:12

Im trying to write a query that will extract a list of unique indexes. Sysconstraints and sysindexes dont seem to be very useful in this respect. Does anyone know where else I might look to get this information.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-02 : 10:59:56

I too admit to having a hard time with the cataliog...DB2 is soooooo much easier...no damn identity columns....but then they limit the size of an object name to 18 bytes...

I thought it would be found in sysconstraints...

but the following doesn't work...I'll keep looking...


USE Northwind
GO

CREATE TABLE myTable99 (col1 int, col2 char(1))

CREATE UNIQUE INDEX IX1 ON myTable99 (col1, col2)
WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

SELECT * FROM sysindexes where name = 'myTable99'
SELECT * FROM sysobjects where name = 'myTable99'


    SELECT c.* 
      FROM sysobjects o
INNER JOIN sysconstraints c 
	ON o.id = c.id
     WHERE Name = 'myTable99'

GO

ps anyone know this?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

dsdeming

479 Posts

Posted - 2003-09-02 : 13:45:48

Completely undocumented, but here's what I found.

create table a( a int )
go

create unique index aa on a( a )
select * from sysindexes where id in( select id from sysobjects where name = 'a' )
select * from sysindexes where status = 2097154
drop table a

The second select contains the status value of the unique index I created. It returned additional hits, all of which proved to be unique indexes. I wouldn't guarantee it'll work, but it could get you started.

Dennis

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-02 : 13:57:49

SELECT * FROM sysobjects where xtype = 'UQ'

xtype will be UQ and type will be K. See SQL Server Books Online for more details about the columns in sysobjects.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-02 : 18:53:50

There's also:

SELECT name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsUnique')=1

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-03 : 09:54:08

ok...holy sh_t

How many other "property" commands are out there, and how do you refer to them in bol?

oh and btw...why isn't it a column in sysindexes like other "normal" databases?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

EDIT: Damn...learned something new again....

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-03 : 13:17:08

There's INDEXPROPERTY, OBJECTPROPERTY, DATABASEPROPERTY, COLUMNPROPERTY, TYPEPROPERTY and SERVERPROPERTY (SQL2K). There might be more, didn't check BOL completely but there is a list in the Functions section.

And the status column in sysindexes will tell you what kind of index it is, but since this can change in future releases you should use the system functions instead.

Just to add, if you think the system tables in SQL Server are hard to deal with you've obviously NEVER looked at MS Access or Oracle.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-03 : 14:23:42

Not so....

Access must have more going on under the covers with it's collections...very painful...and Oracle is just huge...they just store the index_type right in the table...

Have you seen DB2 7 lately? Man that sucker got big as well...

Don't even know what the sysjava collection is for yet....

It just seems like the have to jump through hoops to get to stuff that should be evident in the catalog...

Have you looked at sp_help lately?

I like this comment in their code...(would you code this way?)



		-- IF NOT IN SYSTYPES, GIVE UP
		if @objid is null
		begin
			select @dbname=db_name()
			raiserror(15009,-1,-1,@objname,@dbname)
			return(1)
		end

Is their no RI on the catalog?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-03 : 19:34:40

AFAIK the "RI" on the system tables is handled by the SQL Server process, not through true RI. True RI would only slow down or impede the processes of creating, dropping, or altering objects. Again, another reason why editing the system tables should be avoided.

Between the INFORMATION_SCHEMA views and the various property functions available, you can find out just about everything about the structure of a database without hitting the system tables at all. Can't say I'd call any of it "jumping through hoops", and it's still vastly easier than any other other database product I know of.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-09 : 13:42:53

Possible to get
Index name, column used and whether it is varchar and not null selected...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-09 : 19:54:20

TRACEYSQL, this thread is 4 years old. Start a new thread and always make sure to ask a question. Your post doesn't do that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources