Unable to Pass the value to the DB_ID(). Please suggest the logical error.

Question

Post reply

Unable to Pass the value to the DB_ID(). Please suggest the logical error.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
April 8, 2015 at 1:42 pm

#302836

declare @Str varchar(100)
--@specificDB varchar(100),
DECLARE @dbid int
SELECT @dbid = DB_ID(@specificDB)
print @dbid
--SET @specificDB = 'AdventureWorksDW2012'
--set @Str = 'use ' + '['+@specificDB +']'+';' +CHAR(10);
--exec (@Str)
--PRINT @Str
INSERT INTO #FragmentedIndexes
SELECT
DB_NAME(@dbid) AS DatabaseName
, ss.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(@dbid,NULL, NULL, NULL, 'DETAILED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = @dbid
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0;
select * from #FragmentedIndexes
Thanks.

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

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

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

From what you pasted you need to uncomment the variable declaration and set a value.

But I tested this code and it worked just fine:

DECLARE @specificDB VARCHAR(100);

SET @specificDB = 'AdventureWorks2014';

DECLARE @dbid INT;

SELECT @dbid = DB_ID(@specificDB);

PRINT @dbid;

SELECT DB_NAME(@dbid) AS DatabaseName,

ss.name AS SchemaName,

OBJECT_NAME(s.object_id) AS TableName,

i.name AS IndexName,

s.avg_fragmentation_in_percent AS [Fragmentation%]

FROM sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, 'DETAILED') s

INNER JOIN sys.indexes i

ON s.object_id = i.object_id

AND s.index_id = i.index_id

INNER JOIN sys.objects o

ON s.object_id = o.object_id

INNER JOIN sys.schemas ss

ON ss.schema_id = o.schema_id

WHERE s.database_id = @dbid

AND i.index_id != 0

AND s.record_count > 0

AND o.is_ms_shipped = 0;

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Thanks...

When you pasted the script here, are you enclosing

Code

/code

For the proper indentation and color as is in SSMS?

Thanks.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

One the left side you can see those IFCode Shortcuts. One is ". I just highlight the T-SQL and then click on the value on the left.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Thanks.

I will share with you my final rebuild index script.

please suggest if you see any correction or modification neeeded.

Cheers

Thanks.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

SQL-DBA-01 (4/8/2015)
Thanks.
I will share with you my final rebuild index script.
please suggest if you see any correction or modification neeeded.
Cheers

I'll make a suggestion now, cheat and use Minion Reindex[/url] instead of building your own index maintenance script.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning