Results 1 to 8 of 8

Thread: MS SQL Server

  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Post Unanswered: 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 Bhat

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: MS SQL Server

    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.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  4. #4
    Join Date
    Aug 2003
    Posts
    2

    Re: MS SQL Server

    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •