January 7, 2011 at 6:58 am
Any help would be wonderfull.
This sql works :
use master
select * from model.sys.objects;
But I get an error during creation of a simple procedure :
use master
CREATE PROCEDURE [test] (@DBName char(255)) AS
SELECT * FROM @DBName.sys.objects;
Msg 102, Level 15, State 1, Procedure test, Line 3
Incorrect syntaxe near '.'.
Should I use dynamic sql ?
Any Idea ?
January 7, 2011 at 7:04 am
What are you trying to do exactly (the bigger project).
You cannot use a variable for the db name. It has to be dynamic sql.
January 7, 2011 at 7:18 am
Thanks for the reply.
I didn't know this limit.
Let's use dynamic sql !
January 7, 2011 at 7:32 am
Is it for an admin task of production code?
January 7, 2011 at 8:53 am
The goal is to create a repository database from where admin tasks could be executed.
The stored procedure should reorg/rebuild indexes of a database.
Dynanic sql worked but new problem with variable declaration
CREATE PROCEDURE [Test] (@DBName char(255)) AS
...
DECLARE @objectname nvarchar(130);
...
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @pagecnt;
IF @@FETCH_STATUS < 0 BREAK;
SET @command = N'SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM ' + CONVERT(nvarchar,rtrim(@DBName)) + N'.sys.objects AS o JOIN ' + CONVERT(nvarchar,rtrim(@DBName)) + N'.sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = ' + CONVERT(nvarchar,@objectid) + ';'
EXEC (@command);
...
Error message :
Msg 137, Level 15, State 1, Line 1
Scalar Variable @objectname should be declared.
No idea this time, maybe linked with dynamic sql.
Any Help ?
January 7, 2011 at 9:04 am
The declare needs to be inside the dyn sql.
Since that won't work, you'd need a temp table.
But then again you don't need a loop to do that. A simple insert should do it.
January 7, 2011 at 9:10 am
You're right !
I will keep you informed (weekend starts now).
January 11, 2011 at 4:28 am
I merged everything in one insert command into a temporary table.
But I am still stuck with dynamic sql because I need a variable as database name for sys.objects, sys.schemas, sys.indexes...
The procedure is located in xxxxx database and executed with the parameter yyyyy which is a database name.
The goal is to reorg.rebuild indexes of yyyyy database.
use xxxxx
CREATE PROCEDURE [test] (@DBName char(255)) AS
...
SET @command = N'SELECT
o.name AS objectname,
s.name AS schemaname,
i.name AS indexname,
ips.partition_number AS partitionnum,
ips.avg_fragmentation_in_percent AS frag,
ips.page_count AS pagecntp,
count(p.partition_id) AS partitioncount
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (' + CONVERT(nvarchar,rtrim(@DBId)) + N', NULL, NULL , NULL, ''LIMITED'') AS ips
JOIN ' + CONVERT(nvarchar,rtrim(@DBName)) + N'.sys.objects as o ON o.object_id = ips.object_id
JOIN ' + CONVERT(nvarchar,rtrim(@DBName)) + N'.sys.schemas as s ON s.schema_id = o.schema_id
JOIN ' + CONVERT(nvarchar,rtrim(@DBName)) + N'.sys.indexes as i ON i.object_id = o.object_id AND i.index_id=ips.index_id
JOIN ' + CONVERT(nvarchar,rtrim(@DBName)) + N'.sys.partitions as p ON p.object_id = i.object_id and p.index_id=i.index_id
WHERE ips.avg_fragmentation_in_percent > 10.0
AND ips.index_id > 0
AND ips.page_count > 100
GROUP BY o.name, s.name, i.name, ips.partition_number, ips.avg_fragmentation_in_percent, ips.page_count;'
EXEC (@command);
select * from #work_to_do;
....
I execute the procedure
EXEC test 'yyyyyy';
=> Error message :
Msg 208, Level 16, State 0, Procedure test, Line 60
Invalid object name '#work_to_do'.
It seems that datas of temporary table executed in dynanic sql can't be transfered to the core procedure.
Any idea would be really helpfull.
January 11, 2011 at 5:30 am
No it cannot be transfered. It can be created before the dynamic and used it it tho.
are you doing a defrag script?
If so you can just use this one... it's the most complete out there (that I know of).
January 11, 2011 at 6:39 am
Thanks for the link.
It's really what I want to do.
They had the same problem with dynamic sql.
To solve the issue, they :
- have created "real" tables (for exemple : dba_indexDefragStatus) and made an update inside those tables
OR
- used output variable in dynamic sql
Update inside "real" tables :
SELECT @updateSQL = N'Update ids
Set schemaName = QuoteName(s.name)
, objectName = QuoteName(o.name)
, indexName = QuoteName(i.name)
From dbo.dba_indexDefragStatus As ids
Inner Join ' + @databaseName + '.sys.objects As o
On ids.objectID = o.object_id
Inner Join ' + @databaseName + '.sys.indexes As i
On o.object_id = i.object_id
And ids.indexID = i.index_id
Inner Join ' + @databaseName + '.sys.schemas As s
On o.schema_id = s.schema_id
Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + '
And i.type > 0
And ids.databaseID = ' + CAST(@databaseID AS VARCHAR(10));
EXECUTE SP_EXECUTESQL @updateSQL;
Output variable in dynamic sql :
SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*)
From ' + @databaseName + '.sys.partitions
Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';'
, @partitionSQL_Param = '@partitionCount_OUT int OutPut';
EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount
January 11, 2011 at 6:54 am
Don't reinvent the wheel. There are lots of well written, well tested index maintenance scripts available, like the one Ninja recommended.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply