September 22, 2008 at 8:06 pm
The script must exist somewhere, I believe. Anyone would like to share?
Thank you in advance.
September 23, 2008 at 12:40 am
Vivien Xing (9/22/2008)
The script must exist somewhere, I believe. Anyone would like to share?Thank you in advance.
1 EXEC sp_tables
2 select * from sys.tables
3 select * from sysobjects where xtype='u'
4 select * from sys.objects where type='u'
5 select * from INFORMATION_SCHEMA.TABLES where table_type='base table'
Failing to plan is Planning to fail
September 23, 2008 at 3:01 am
select o.name from sys.indexes i
inner join sys.objects o
on o.object_id = i.object_id
where i.index_id = 0
September 23, 2008 at 3:19 am
Suresh B. (9/23/2008)
select o.name from sys.indexes iinner join sys.objects o
on o.object_id = i.object_id
where i.index_id = 0
Why do you think it is neccessary to join sys.indexes?
Failing to plan is Planning to fail
September 23, 2008 at 3:25 am
Becuase the subject says
script needed - to lists all the tables without a clustered index - sql2k5/2k
September 23, 2008 at 3:33 am
When you create a table without cluster index, a row is inserted in the sysindexes table marking this table is a HEAP. This is show by the indid field.
In SQL Server 2000 the query is:
select o.name from sysindexes i
inner join sysobjects o
on o.id = i.id
where i.indid = 0
September 25, 2008 at 8:11 pm
Thank you all for help. I limited to the user tables only.
Here is the code with minor change:
-- SQL2005:
select o.name from sys.indexes i
inner join sys.objects o
on o.object_id = i.object_id
where o.type = 'U' and i.index_id = 0
-- SQL2000:
select o.name, i.indid from sysindexes i
inner join sysobjects o
on o.id = i.id
where o.xtype = 'u' and i.indid = 0
September 26, 2008 at 8:03 am
Suresh B. (9/23/2008)
Becuase the subject saysscript needed - to lists all the tables without a clustered index - sql2k5/2k
Well. I did nto see the title fully 🙂
Failing to plan is Planning to fail
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply