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;
Best Answer
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.
The second query will report for each table the identity column, if any on each table in your database.
To limit the queries to a specific table add a
WHERE
clause similar to: