Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am looking at a SQL Server database for an application I have inherited. I have not looked into SQL Server for approximately 10 years, so please bear with me.

The database table I am looking at has a bigint NOT NULL column called id, yet, when I check for constraints, I don't see any, and the same holds true for all database tables.

Am I right in assuming that there is no primary key & no indexing (clustered or nonclustered) on these tables?

I ran the following queries and the results appear to confirm my suspicion:

//**returns 0**
select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

//**returns no rows**
select * from sys.indexes
where object_id = (select object_id from sys.objects where name = 'NAME-OF-TABLE');

//**returns all tables in database**
SELECT name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0;
share|improve this question

3 Answers 3

up vote 1 down vote accepted

These two queries may help you. The first will list all of the tables and indexes on those tables in your database. If the table does not appear in the list is does not have any indexes defined on it. These queries assume SQL Server version 2005 or newer.

SELECT 
    I.name AS IndexName
    , T.name AS TableName
    , I.is_primary_key AS IsPrimaryKey

FROM sys.indexes AS I
    INNER JOIN sys.tables AS T
        ON ( I.object_id = T.object_id )

ORDER BY T.name, I.name;

The second query will report for each table the identity column, if any on each table in your database.

SELECT 
    T.name AS TableName
    , COALESCE(C.name, 'No identity column') AS IdentityColumn

FROM sys.tables AS T
    LEFT OUTER JOIN sys.columns AS C
        ON ( T.object_id = C.object_id AND C.is_identity = 1 )

ORDER BY T.name;

To limit the queries to a specfic table add a WHERE clause similar to:

WHERE T.name = 'NAME-OF-TABLE'
share|improve this answer
    
thank you for sharing. –  ali haider Apr 14 at 20:45

No, something is incorrect.

The check on sys.indexes should return a row even if your table has no indexes. The heap still has a record in sys.indexes with a type_desc of 'HEAP' and type of 0.

I think you probably need to make sure you are in the right database context since OBJECT_ID() and sys.objects are database-specific.

Try this:

USE MyDatabase

SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('schema.MyTableName')
share|improve this answer
    
thanks - I updated my post - the first query returns 0. –  ali haider Apr 14 at 20:00

I am not sure if you're interested in all constraints but INFORMATION_SCHEMA.TABLE_CONSTRAINTS doesn't seem to return the DEFAULT constraints -- TABLE_CONSTRAINTS (Transact-SQL)

CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

This query will do a simple count against the sys.objects DMV:

select COUNT(*)
from sys.objects o
where o.type_desc like '%CONSTRAINT%';

If you are interested in listing the tables, you could run something like this:

select distinct
   o.object_id
 , QUOTENAME(s.name) + '.' + QUOTENAME(o.name) as [object_name]
 , o.type_desc
 , case when dc.parent_object_id is null then 'No' else 'Yes' end as has_default_constraint
 , case when cc.parent_object_id is null then 'No' else 'Yes' end as has_check_constraint
 , case when fk.parent_object_id is null then 'No' else 'Yes' end as has_foreing_key
 , case when kc.parent_object_id is null then 'No' else 'Yes' end as has_primary_key
from sys.objects o
inner join sys.schemas s on s.schema_id = o.schema_id
left outer join sys.default_constraints dc on dc.parent_object_id = o.object_id and dc.schema_id = o.schema_id
left outer join sys.check_constraints cc on cc.parent_object_id = o.object_id and cc.schema_id = o.schema_id
left outer join sys.foreign_keys fk on fk.parent_object_id = o.object_id and fk.schema_id = o.schema_id
left outer join sys.key_constraints kc on kc.parent_object_id = o.object_id and kc.schema_id = o.schema_id
where o.is_ms_shipped = 0
  and o.type = 'U'
order by [object_name];

This one should give you the info on your indexes:

select o.name
 , i.*
from sys.objects o
inner join sys.indexes i on i.object_id = o.object_id
where o.is_ms_shipped = 0
  and i.object_id > 100
  and i.index_id > 0
order by o.name
   , i.index_id;
  • Index_Id = 0 -- HEAP (won't show up)
  • Index_Id = 1 -- CLUSTERED
  • Index_Id > 1 -- NONCLUSTERED
share|improve this answer
    
thank you for sharing - quite helpful –  ali haider Apr 14 at 20:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.