SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database_Object_Last_used_details


Database_Object_Last_used_details

Author
Message
abhijitshedulkar
abhijitshedulkar
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 615
Comments posted to this topic are about the item Database_Object_Last_used_details
hp_dba_uk
hp_dba_uk
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 123
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?
aleksey donskoy
aleksey donskoy
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 519
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
abhijitshedulkar
abhijitshedulkar
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 615
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:-)
abhijitshedulkar
abhijitshedulkar
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 615
Thank Forum Newbie for your response!!!!!:-)
Jorge Serres
Jorge Serres
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 201
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),
ServerName varchar(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
abhijitshedulkar
abhijitshedulkar
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 615
Thanks for your reply

when we executed your provided script, DB name not showing properly plz check the same
Jorge Serres
Jorge Serres
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 201
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),
ServerName varchar(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
Iwas Bornready
Iwas Bornready
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29592 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search