Database_Object_Last_used_details

  • Comments posted to this topic are about the item Database_Object_Last_used_details

  • Msg 8152, Level 16, State 2, Line 8

    String or binary data would be truncated.

    The statement has been terminated.

    (0 row(s) affected)

    Get above? Any idea. running in 2005

    Tested individual statements and they work..but nothing inserted in the UnusedObjectlist

    Also using DMV - which gets wiped out every restart - wouldnt this be a concern?

  • Awesome script.

    Had to correct database name and table name handling in multiple places. The were only 20 characters long made them all 128 characters. As well as the the database name bracketing [] as it was filing where DB_NAME() met a space...

    Thanks

    Alex

  • I think you need copied script properly. Because It working properly in our all clients DB server.

    Please recheck the same!!

    Thanks & regards

    Abhijit Y S:-)

  • Thank Forum Newbie for your response!!!!!:-)

  • Would first name the DBs correctly ('master','tempdb','msdb','model') >>>> mode vs model

    Would add sufficient space in Table Var to DBName Column (could be a sysname data type)

    Would qualify the database names within the dyanmic sql to use []. brackets;

    select distinct(name),type_desc,' + '''' + @@SERVERNAME + '''' + ',' + '''[ + @DBName + ]'''

    Would add error handling to the script too!

    For example if you have a non existing object such as in database ReportServer

    Invalid object name 'ReportServer$DENALIT.sys.objects;

    Here's the modified script:

    -- drop procedure [dbo].[Database_Object_Last_used_details]

    Create procedure [dbo].[Database_Object_Last_used_details]

    as

    BEGIN

    BEGIN TRY

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UnusedObjectlist]') AND type in (N'U'))

    DROP TABLE [dbo].[UnusedObjectlist]

    CREATE TABLE [dbo].[UnusedObjectlist](

    ObjectName varchar(50),

    ObjectType varchar(25),

    ServerNamevarchar(20),

    DatabaseName varchar(20),

    CreatedDate Datetime,

    ModifyDate datetime,

    last_usedDate datetime

    )

    declare @CountDataBase as int

    set @CountDataBase = 0

    set @CountDataBase = (SELECT count(name) FROM master..sysdatabases where name not in ('master','tempdb','msdb','model'))

    Declare @GetDataBase as table ([Id] [bigint] IDENTITY(1,1) NOT NULL,DBName sysname)

    insert into @GetDataBase (DBName) (SELECT name FROM master..sysdatabases where name not in ('master','tempdb','msdb','model'))

    declare @StartCounter as int

    set @StartCounter = 1

    while(@StartCounter <= @CountDataBase)

    begin

    declare @DBName as varchar(20)

    set @DBName = ''

    set @DBName = (select DBName from @GetDataBase where Id = @StartCounter)

    declare @Query1 nvarchar(Max)

    set @Query1 = 'insert into UnusedObjectlist

    select distinct(name),type_desc,' + '''' + @@SERVERNAME + '''' + ',' + '''[ + @DBName + ]''' + ',create_date,modify_date,max(last_user_update) as last_used

    from ' + @DBName + '.sys.objects a left outer join ' + @DBName + '.sys.dm_db_index_usage_stats b

    on a.object_id = b.object_id

    where type_desc IN (' + '''' + 'USER_TABLE' + '''' + ',' + '''' + 'VIEW' + '''' + ',' + '''' + 'SQL_SCALAR_FUNCTION' + '''' + ',' + '''' + 'SQL_STORED_PROCEDURE' + '''' + ',' + '''' + 'SQL_SCALAR_FUNCTION' + '''' + ')

    group by name,create_date,modify_date,type_desc

    order by 4 desc '

    exec (@Query1)

    declare @Query2 nvarchar(Max)

    set @Query2 =

    '

    update UnusedObjectlist set last_usedDate = b.last_execution_time

    FROM ' + @DBName + '.sys.objects a left outer join ' + @DBName + '.sys.dm_exec_procedure_stats b

    on a.object_id = b.object_id

    join UnusedObjectlist c on a.name collate SQL_Latin1_General_CP1_CI_AS = c.ObjectName

    where a.type_desc=' + '''' + 'SQL_STORED_PROCEDURE' + '''' +

    ' and b.last_execution_time is not null

    and c.last_usedDate is null '

    exec(@Query2)

    set @StartCounter = @StartCounter + 1

    end

    END TRY

    BEGIN CATCH

    select error_number() as errornumber

    ,error_message() as errormessage;

    END CATCH

    END

    select * from UnusedObjectlist

    GO

  • Thanks for your reply

    when we executed your provided script, DB name not showing properly plz check the same

  • Oops! Typo..here it is

    Create procedure [dbo].[Database_Object_Last_used_details]

    as

    BEGIN

    BEGIN TRY

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UnusedObjectlist]') AND type in (N'U'))

    DROP TABLE [dbo].[UnusedObjectlist]

    CREATE TABLE [dbo].[UnusedObjectlist](

    ObjectName varchar(50),

    ObjectType varchar(25),

    ServerNamevarchar(20),

    DatabaseName varchar(20),

    CreatedDate Datetime,

    ModifyDate datetime,

    last_usedDate datetime

    )

    declare @CountDataBase as int

    set @CountDataBase = 0

    set @CountDataBase = (SELECT count(name) FROM master..sysdatabases where name not in ('master','tempdb','msdb','model'))

    Declare @GetDataBase as table ([Id] [bigint] IDENTITY(1,1) NOT NULL,DBName sysname)

    insert into @GetDataBase (DBName) (SELECT name FROM master..sysdatabases where name not in ('master','tempdb','msdb','model'))

    declare @StartCounter as int

    set @StartCounter = 1

    while(@StartCounter <= @CountDataBase)

    begin

    declare @DBName as varchar(20)

    set @DBName = ''

    set @DBName = (select DBName from @GetDataBase where Id = @StartCounter)

    declare @Query1 nvarchar(Max)

    set @Query1 = 'insert into UnusedObjectlist

    select distinct(name),type_desc,' + '''' + @@SERVERNAME + '''' + ',' + '''[' + @DBName + ']''' + ',create_date,modify_date,max(last_user_update) as last_used

    from ' + @DBName + '.sys.objects a left outer join ' + @DBName + '.sys.dm_db_index_usage_stats b

    on a.object_id = b.object_id

    where type_desc IN (' + '''' + 'USER_TABLE' + '''' + ',' + '''' + 'VIEW' + '''' + ',' + '''' + 'SQL_SCALAR_FUNCTION' + '''' + ',' + '''' + 'SQL_STORED_PROCEDURE' + '''' + ',' + '''' + 'SQL_SCALAR_FUNCTION' + '''' + ')

    group by name,create_date,modify_date,type_desc

    order by 4 desc '

    exec (@Query1)

    declare @Query2 nvarchar(Max)

    set @Query2 =

    '

    update UnusedObjectlist set last_usedDate = b.last_execution_time

    FROM ' + @DBName + '.sys.objects a left outer join ' + @DBName + '.sys.dm_exec_procedure_stats b

    on a.object_id = b.object_id

    join UnusedObjectlist c on a.name collate SQL_Latin1_General_CP1_CI_AS = c.ObjectName

    where a.type_desc=' + '''' + 'SQL_STORED_PROCEDURE' + '''' +

    ' and b.last_execution_time is not null

    and c.last_usedDate is null '

    exec(@Query2)

    set @StartCounter = @StartCounter + 1

    end

    END TRY

    BEGIN CATCH

    select error_number() as errornumber

    ,error_message() as errormessage;

    END CATCH

    END

    select * from UnusedObjectlist

    GO

  • Thanks for the script.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply