MS SQL Server

Hi ,

I have a lot of tables in a database on MS SQl Sever 7.0
if i want to find out if any tables are not being used by any application...
Like, A table is there and it is no longer is used, but how to find out if that is really not used by any front end applications or any BCP/BULKINSERT statements etc.
Is there any way ? will the profiles help?

Thanks for the answer

Subrahmanya BhatAs far as I know, there is no "Last Accessed" property of a table.

If you want to identify all un-used tables, you may consider to catch all tables with changing record counts in the first place. Make a UNION query of counts of all tables like

SELECT count(*), "Table 1" FROM [Table 1] UNION
SELECT count(*), "Table 2" FROM [Table 2] UNION
...

, make an initial count, and repeat this after a while.

All tables with stable record counts may be unused, or just static like domain tables. To distinguish between them, rename all those tables by adding something like XYZ to the name, and test all your applications. If you are lucky, you will get a list of "missing" tables, which are the static ones.

However, this is a difficult path with an unknown result. Maybe your applications do not report the missing table, but just disfunctioning. Or you don't have a complete list of all applications (including import or export functionality with some auxillary tables). So, my advise would be to let the database as it is.|||Use SQL Profiler to monitor the tables in question.
You will have to run it long enough to be confident that all applications have been active. I'd say at least a month in order to cover typical month-end processing.

blindman|||Thanks for the information provided(Both cunt(*) and profile)
hopefully i find out such tables in my database using these suggestions.

Yeah if there was a "Last Accessed" property that would have been great

i am just planning to use DoktirBlue's method to begin with
with a little different approach liek below,

DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'user table' ( or some 'Tab%')

and open the cursor and from each table get the count(*) and insert in to a temp table

Just thinking in these lines , will see how far i will be successful.

Thanks for the help again
Sub rahmanya Bhat

Originally posted by DoktorBlue
As far as I know, there is no "Last Accessed" property of a table.

If you want to identify all un-used tables, you may consider to catch all tables with changing record counts in the first place. Make a UNION query of counts of all tables like

SELECT count(*), "Table 1" FROM [Table 1] UNION
SELECT count(*), "Table 2" FROM [Table 2] UNION
...

, make an initial count, and repeat this after a while.

All tables with stable record counts may be unused, or just static like domain tables. To distinguish between them, rename all those tables by adding something like XYZ to the name, and test all your applications. If you are lucky, you will get a list of "missing" tables, which are the static ones.

However, this is a difficult path with an unknown result. Maybe your applications do not report the missing table, but just disfunctioning. Or you don't have a complete list of all applications (including import or export functionality with some auxillary tables). So, my advise would be to let the database as it is.|||Its not necessary to use a cursor. For example, this query of the system tables could be used to insert data directly into a rowcount history table:

select sysobjects.name, sysindexes.rows
from sysobjects
inner join sysindexes on sysobjects.id = sysindexes.id and sysindexes.indid < 2
where sysobjects.type <> 'S'

You can expand on this to get a lot more functionality.

blindman|||bm, isn't your profiler option powerful enough? Anyway, thanks for your showing an easy way to get the row count. But, is this count always accurate. Isn't it saver to run a statistics update before like

dbcc updateusage
go

? Also, you should exclude this 'dtproperties' from your query.|||Yes, the Profiler option would be more thorough, but for a quick check against active tables (not lookup or reference tables!) the rowcount should be sufficient.

Update stats does need to be run in order to make 100% sure that the rowcount is accurate, though it should be accurate anyway. I do not use the SCHEMA objects much, since I normally query system tables directly, but I would guess that they reference the system tables too, and are therefor equally subject to rowcount errors if the statistics are out of date.|||along with dtproperties, we can also filter out everything else that comes with the server install by changing the bm's code to look like this:

select sysobjects.name, sysindexes.rows
from sysobjects
inner join sysindexes on sysobjects.id = sysindexes.id and sysindexes.indid < 2
where objectproperty(sysobjects.id, 'IsMSShipped') = 0