September 12, 2012 at 2:05 am
Comments posted to this topic are about the item Database_Object_Last_used_details
September 12, 2012 at 5:37 am
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?
September 12, 2012 at 9:56 am
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
September 13, 2012 at 3:13 am
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:-)
September 13, 2012 at 3:15 am
Thank Forum Newbie for your response!!!!!:-)
September 13, 2012 at 3:32 pm
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
September 17, 2012 at 4:02 am
Thanks for your reply
when we executed your provided script, DB name not showing properly plz check the same
September 17, 2012 at 10:48 pm
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
May 9, 2016 at 1:26 pm
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