Want to add databasename in the results.

Question

Post reply

Want to add databasename in the results.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
April 6, 2015 at 2:52 pm

#306465

Guys
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. Can you suggest how I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR
SELECT NAME
FROM sys.databases
WHERE database_id IN (SELECT dbid
FROM sys.sysdatabases
EXCEPT
SELECT database_id
FROM msdb..suspect_pages)
AND NAME NOT IN ( 'master', 'msdb', 'tempdb', 'model',
'distribution' )
AND state_desc = 'ONLINE'
OPEN databasecursor
FETCH next FROM databasecursor INTO @DB
IF @@FETCH_STATUS <> 0
RETURN
WHILE @@FETCH_STATUS = 0
BEGIN
--Print @DB
SET @sql = 'USE ' + ' ' + Quotename(@DB) + ';'
+
'SELECT objectname=Object_name(i.object_id), indexname=i.NAME, o.create_date, o.modify_date FROM sys.indexes i,
sys.objects o WHERE Objectproperty(o.object_id, ''IsUserTable'') = 1 AND i.index_id NOT IN (SELECT s.index_id
FROM sys.dm_db_index_usage_stats s WHERE s.object_id = i.object_id AND i.index_id = s.index_id AND db_name(database_id) = '''
+ Cast(@DB AS VARCHAR(10))
+ ''')
AND o.object_id = i.object_id
ORDER BY objectname, i.index_id, indexname ASC, o.modify_date desc'
--Print @sql;
EXEC(@sql)
--EXECUTE sp_executesql @sql
--EXEC (@sql)
FETCH next FROM databasecursor INTO @DB
END
CLOSE databasecursor
DEALLOCATE databasecursor
Thanks.

Viewing 3 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Using DB_NAME() is an easy option.

SELECT DBname= DB_NAME()

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

many thanks. After your post I recalled that db_name(), db_id(), @@servername are all can be used / concatenated easily.

Thanks.