March 10, 2005 at 1:45 pm
I recently switched jobs and inherited alot of messy Databases that I am trying to clean up. Is there any way to tell when an object (mainly tables and stored procedures) has last been used?
March 10, 2005 at 3:17 pm
Not sure if there is a last accessed column or similar in any system tables.
Like you, I will be in a new job next week and will be in a similar situation...Strange database and no idea of how it pulls together. Hopefully I'll have some good documentation to look through.
My advice to you would be to run a profiler trace on your databases. You will be able to determine which objects are accessed, the frequency and approximately how long the queries/sp's take to run at various times of the day.
I have a script somewhere which was supposed to tell you all objects that were not accessed in a set period. I never did test it out, but I'll dig it out and post it as a script sometime.
March 11, 2005 at 5:38 am
I was in the same situation and wrote this to help me trace where things are used. You can fill in any table and it will tell you what is in it or any column name and set it to search out in any or all tables, views, stored procedures, User functions where the column name is used. There are flags to use like for the table or column name you supply. You can also play with the sorting. Hope it helps.
--By Rick Carisse
--This is used to analyze a database's structure and relationships
--e.g. what fields are in a table or all tables, in which tables, views, stored procedures, functions is a column referenced
declare @NameLen int
declare @TypeLen int
declare @Script varchar(2000)
declare @TableWanted varchar(128)
declare @ColumnWanted varchar(128)
declare @SeeWhat varchar(10)
declare @Where varchar (200)
declare @OrderBy varchar (100)
declare @UseLikeForTableName bit
declare @UseLikeForColName bit
--Modify the following lines to vary the results as desired.
--@TableWanted can be any table or view in the current database
--@ColumnWanted can be any column in the current database
--@SeeWhat can be T (tables), V (views), P (stored procedures), F (User functions), All (all 4), or any combination. Default is All 4.
--@UseLikeForTableName and @UseLikeForColName are flags that set whether to use Like or = to search for table or column name
--When specifying all columns for a single table an approximate count of maximum row size it returned
--The row size is approximate because it doesn't account for sql server overhead for various data types
set @TableWanted = 'All'
set @ColumnWanted = 'All'
set @SeeWhat = 'All'
set @UseLikeForTableName = 0
set @UseLikeForColName = 0
set @OrderBy = 'order by ColType, ColName, ColSize, Parent, c.colorder'
--Don't change anything from here down.
set @Where = 'where ('
if (SELECT CHARINDEX('T', @SeeWhat)) > 0
set @Where = @Where + 's.xtype = ' + quotename('U', '''')
if (SELECT CHARINDEX('V', @SeeWhat)) > 0
if @Where = 'where ('
begin
set @Where = @Where + 's.xtype = ' + quotename('V', '''')
end
else
begin
set @Where = @Where + ' or s.xtype = ' + quotename('V', '''')
end
if (SELECT CHARINDEX('P', @SeeWhat)) > 0
if @Where = 'where ('
begin
set @Where = @Where + 's.xtype = ' + quotename('P', '''')
end
else
begin
set @Where = @Where + ' or s.xtype = ' + quotename('P', '''')
end
if (SELECT CHARINDEX('F', @SeeWhat)) > 0
if @Where = 'where ('
begin
set @Where = @Where + 's.xtype = ' + quotename('FN', '''')
end
else
begin
set @Where = @Where + ' or s.xtype = ' + quotename('FN', '''')
end
if @Where = 'where ('
begin
set @Where = @Where + 's.xtype = ' + quotename('U', '''') + ' or s.xtype = ' + quotename('V', '''')
+ ' or s.xtype = ' + quotename('P', '''') + ' or s.xtype = ' + quotename('FN', '''')
end
set @Where = @Where + ') and (s.status >= 0) '
if @TableWanted <> 'All'
begin
if @UseLikeForTableName = 0
begin
set @Where = @Where + 'and (s.name = ' + quotename(@TableWanted, '''') + ') '
end
else
begin
set @Where = @Where + 'and (s.name like ' + quotename('%' + @TableWanted + '%', '''') + ') '
end
if @ColumnWanted <> 'All'
begin
if @UseLikeForColName = 1
set @Where = @Where + 'and (c.name like ' + quotename('%' + @ColumnWanted + '%', '''') + ') '
else
set @Where = @Where + 'and (c.name = ' + quotename(@ColumnWanted, '''') + ') '
end
end
else
begin
if @ColumnWanted <> 'All'
begin
if @SeeWhat <> 'All'
begin
if @UseLikeForColName = 0
set @Where = @Where + 'and (c.name = ' + quotename(@ColumnWanted, '''') + ') '
else
set @Where = @Where + 'and (c.name like ' + quotename('%' + @ColumnWanted + '%', '''') + ') '
end
else
begin
set @Where = 'where c.name = ' + quotename(@ColumnWanted, '''') + ' '
if @UseLikeForColName = 1
set @Where = 'where c.name like ' + quotename('%' + @ColumnWanted + '%', '''') + ' '
end
end
end
set @NameLen = (select max(len(name)) from syscolumns)
set @TypeLen = (select max(len(name)) from systypes)
set @Script =
'select
cast(c.name as varchar(' + cast(@NameLen as varchar(3)) + ')) as ColName,
cast(t.name as varchar(' + cast((select @TypeLen) as varchar(3)) + ')) as ColType,
case t.name
when '+ quotename('nvarchar', '''') + 'then c.prec
when ' + quotename('nchar', '''') + ' then c.prec
when ' + quotename('ntext', '''') + ' then c.prec
else c.length
end as ColSize,
case c.status & 0x80
when 0x80 then ' + quotename('Identity column', '''') + '
else ' + quotename('', '''') + '
end as MoreInfo,
s.name as Parent
from syscolumns c
inner join sysobjects s on c.id = s.id
inner join systypes t on c.xusertype = t.xusertype
'
set @Script = @Script + @Where
set @Script = @Script + '
'
if @TableWanted <> 'All' and @UseLikeForTableName = 0 and @ColumnWanted = 'All'
exec('select sum(x.ColSize) as ' + @TableWanted + '_MaxRowSize from (' + @Script + ') x')
exec(@Script + @OrderBy)
Rick
March 11, 2005 at 5:48 am
Nice one Rick. I'll be sure to try that out over the next couple weeks!
March 11, 2005 at 6:25 am
Thanks for the help you two. I will try that out Rick!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply