Friday, March 9, 2012
How do I find unique indexes in SQL-server 2005.
At the moment I am locking at contraints in general.
Most I can find in the information_schema views.
(Is there a webpage or -site which does describe this ?).
But how do I find indexes which are not implemented as constraints,
especially 'unique - indexes' ?
Thanks for your time and attention,
ben brugman
select sysobjects.name from sysobjects join
sysindexes on sysobjects.id=sysindexes.id
where sysindexes.status=2 and type='u'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:OaBHeCg5GHA.1256@.TK2MSFTNGP04.phx.gbl...
>I am trying to get to grips with a database.
> At the moment I am locking at contraints in general.
> Most I can find in the information_schema views.
> (Is there a webpage or -site which does describe this ?).
> But how do I find indexes which are not implemented as constraints,
> especially 'unique - indexes' ?
> Thanks for your time and attention,
> ben brugman
>
How do I find unique indexes in SQL-server 2005.
At the moment I am locking at contraints in general.
Most I can find in the information_schema views.
(Is there a webpage or -site which does describe this ?).
But how do I find indexes which are not implemented as constraints,
especially 'unique - indexes' ?
Thanks for your time and attention,
ben brugmanselect sysobjects.name from sysobjects join
sysindexes on sysobjects.id=sysindexes.id
where sysindexes.status=2 and type='u'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:OaBHeCg5GHA.1256@.TK2MSFTNGP04.phx.gbl...
>I am trying to get to grips with a database.
> At the moment I am locking at contraints in general.
> Most I can find in the information_schema views.
> (Is there a webpage or -site which does describe this ?).
> But how do I find indexes which are not implemented as constraints,
> especially 'unique - indexes' ?
> Thanks for your time and attention,
> ben brugman
>
How do I find unique indexes in SQL-server 2005.
At the moment I am locking at contraints in general.
Most I can find in the information_schema views.
(Is there a webpage or -site which does describe this ?).
But how do I find indexes which are not implemented as constraints,
especially 'unique - indexes' ?
Thanks for your time and attention,
ben brugmanselect sysobjects.name from sysobjects join
sysindexes on sysobjects.id=sysindexes.id
where sysindexes.status=2 and type='u'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:OaBHeCg5GHA.1256@.TK2MSFTNGP04.phx.gbl...
>I am trying to get to grips with a database.
> At the moment I am locking at contraints in general.
> Most I can find in the information_schema views.
> (Is there a webpage or -site which does describe this ?).
> But how do I find indexes which are not implemented as constraints,
> especially 'unique - indexes' ?
> Thanks for your time and attention,
> ben brugman
>
Wednesday, March 7, 2012
How do I find IDENTITY columns on Table using T-SQL
Found it, a little obscure:
SELECT obj.[name], col.[name], col.[colstat], col.*
FROM [syscolumns] col
JOIN [sysobjects] obj
ON obj.[id] = col.[id]
WHERE obj.type = 'U'
AND col.[status] = 0x80
ORDER BY obj.[name]
Does anyone know a way of doing this using an INFORMATIO_SCHEMA view?
|||I posted that sometime ago:SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
FROM syscolumns WHERE OBJECT_NAME(id) = sometable_test'
Mit Information_schema views from
http://weblogs.asp.net/psteele/archive/2003/12/03/41051.aspx
select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1
order by TABLE_NAME
HTH, Jens Suessmeyer.
http://www.sqlserver2005.,de
|||Here is some more ( in technicolor ;-) )
USE northwind
GO
DECLARE @.tableName VARCHAR(50)
SELECT @.tableName = 'orders'
--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @.tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO
DECLARE @.intObjectID INT
SELECT @.intObjectID =OBJECT_ID('orders')
--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@.intObjectID, 'TableHasIdentity'),0) AS HasIdentity
Denis the SQL Menace
http://sqlservercode.blogspot.com/