Where does indexes stored in the SQL server system tables

[Ali Alhussein]

hi, if exists (select * from sysobjects where id = object_id('dbo.MRDD_FINAL&#39 and sysstat & 0xf = 3)
drop table dbo.MRDD_FINAL

This code was generated when I used the create a script to build a table from an existing table.
is there a way to check if a a table contains data or not,
The whole idea is to check if table A contains data, I need to truncate the table,otherwise I do nothing...
regards

Ali

[Ray Miao]

On 11/19/98 8:25:22 AM, Ali Alhussein wrote:
> hi, if exists (select * from sysobjects where id =
> object_id('dbo.MRDD_FINAL&#39 and sysstat & 0xf = 3)
drop table
> dbo.MRDD_FINAL

This code was generated when I used the create a script
> to build a table from an existing table.
is there a way to check if a a
> table contains data or not,
The whole idea is to check if table A
> contains data, I need to truncate the table,otherwise I do nothing...
regards

Ali

Use 'sp_spaceused table_name' to check this. It'll give you how many rows in the table (if any) and space used (both data & index).

[Ali Alhussein]

Thanks for your help, would you also tell me how to capture the output of 'sp_spaceused table_name' in a variable, so I can create a store procedure to update a traking table and save "It'll give you how many rows in the table (if any) and space used (both data & index " your quote..
Is that possible?

thanks Ali

On 11/19/98 8:43:59 AM, Ray Miao wrote:
> On 11/19/98 8:25:22 AM, Ali Alhussein wrote:
> hi, if exists (select *
> from sysobjects where id =
> object_id('dbo.MRDD_FINAL&#39 and
> sysstat & 0xf = 3)
drop table
> dbo.MRDD_FINAL

This code was
> generated when I used the create a script
> to build a table from an
> existing table.
is there a way to check if a a
> table contains data or
> not,
The whole idea is to check if table A
> contains data, I need to
> truncate the table,otherwise I do nothing...
regards

Ali

Use
> 'sp_spaceused table_name' to check this. It'll give you how
> many rows in the table (if any) and space used (both data & index).

[VK]

SYSINDEXES - this is your table.
'dpages' - number of pages used
'rows' - number of rows in the table
make sure rows which you take have 'indid' in (0,1)
You'd better read about this in Books Online...

VK

[JL]

On 11/19/98 9:55:09 AM, VK wrote:
> SYSINDEXES - this is your table.
'dpages' - number of pages
> used
'rows' - number of rows in the table
make sure rows which
> you take have 'indid' in (0,1)
You'd better read about this in
> Books Online...

VK

Yes, but don't forget the DBCC

DBCC UPDATEUSAGE (0, YourTable)
DECLARE @rows int
SELECT @rows = rows FROM sysindexes
WHERE indid IN ( 0, 1, 255 ) AND id = OBJECT_ID( 'YourTable' )
IF @rows = 0
...