rename tables with 0 record counts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

rename tables with 0 record counts

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-04-01 : 14:13:33

I want to write a proc that will loop through a database and if a table has 0 record counts then the table is renamed to 'ZZZ_tablename'. Should I use this to get the row counts? or what is the best way to do this?

DBCC UPDATEUSAGE(GCMC_DISCGRX) WITH COUNT_ROWS

select object_name(id) as table_name,rows from sysindexes
where indid<2 AND INDID = 0 and rows = 0
order by 1

then EXEC sp_rename @emptytables, 'ZZZ_' + @emptytables

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2008-04-01 : 15:39:38

Check this query this can help you in getting all user tables that has 0 records.

SELECT * FROM sysobjects WHERE xtype = 'U' and name in (select object_name(id) from sysindexes
where INDID = 0 and rows = 0 and OBJECTPROPERTY(id,'istable')=1)

Kapil Arya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-02 : 05:02:39

quote:
Originally posted by kapilarya

Check this query this can help you in getting all user tables that has 0 records.

SELECT * FROM sysobjects WHERE xtype = 'U' and name in (select object_name(id) from sysindexes
where INDID = 0 and rows = 0 and OBJECTPROPERTY(id,'istable')=1)

Kapil Arya

You need to run this for accurate count'

DBCC Updateusage('dbname') with count_rows

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources