July 14, 2012 at 1:07 am
Hi All
I'm trying to get out of using cursors to solve my T-SQL problems
Without using a cursor,
I need to write a query that returns a list of tables and I need to do something to each of those tables but it needs to be done one after each other.
How would I go about doing this?
Table List:
SELECT
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.[rowmodctr] AS [ROWS_MODIFIED],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE],
STATS_DATE(sysindexes.id,sysindexes.indid) as [StatisticsDate]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
AND STATS_DATE(sysindexes.id,sysindexes.indid) < = '20120810'
ORDER BY rows DESC
Any help with this would be great
Thanks
July 14, 2012 at 6:10 am
One possible method is to use a temporary table for example:
CREATE TABLE #T(Id INT IDENTITY(1,1),Tname VARCHAR(100),NumRows INT,RowsMod INT,Rspace INT, Uspace INT, StatDate DATETIME)
INSERT INTO #T(Tname,NumRows,RowsMod,Rspace,Uspace,StatDate)
SELECT -- remainder of your code not displayed
-- Partial results -- shortened for illustration only
SELECT * FROM #T
Id Tname NumRows RowsMod Rspace UspaceStatDate
1 Order Details 2155 0 41 352009-08-28 16:43:23.660
2 Orders 830 0 63 602009-08-28 16:43:23.597
July 14, 2012 at 8:51 am
bitbucket-25253 (7/14/2012)
One possible method is to use a temporary table for example:
CREATE TABLE #T(Id INT IDENTITY(1,1),Tname VARCHAR(100),NumRows INT,RowsMod INT,Rspace INT, Uspace INT, StatDate DATETIME)INSERT INTO #T(Tname,NumRows,RowsMod,Rspace,Uspace,StatDate)
SELECT -- remainder of your code not displayed
-- Partial results -- shortened for illustration only
SELECT * FROM #T
Id Tname NumRows RowsMod Rspace UspaceStatDate
1 Order Details 2155 0 41 352009-08-28 16:43:23.660
2 Orders 830 0 63 602009-08-28 16:43:23.597
Thanks
Once the Temp Table has been populated, how would I select the first Tname, do something do that table, then select the next Tname and do something to that table, etc..
Thanks
July 14, 2012 at 11:01 am
Hi,
why don't you use a while loop here?.
select @maxrow = max(id) from #T
SET @row = 1
While (@row < @maxrow )
Begin
select x, y,... form #T where id = @row
...... do something
End
Reji PR
Thanks.
Reji PR,
Bangalore
😀
July 14, 2012 at 11:47 am
Reji PR (7/14/2012)
Hi,why don't you use a while loop here?.
select @maxrow = max(id) from #T
SET @row = 1
While (@row < @maxrow )
Begin
select x, y,... form #T where id = @row
...... do something
End
Reji PR
I'm struggling to get this right
Can you show me with a basic example
Thanks
July 14, 2012 at 2:08 pm
Not knowing what it is you are doing, using a cursor may be the correct thing to do. They do make sense for maintenance rountines and that sort of processing. I have used cursors and/or while loops to perform index maintenance on tables.
July 15, 2012 at 3:06 am
Lynn Pettis (7/14/2012)
Not knowing what it is you are doing, using a cursor may be the correct thing to do. They do make sense for maintenance rountines and that sort of processing. I have used cursors and/or while loops to perform index maintenance on tables.
Thanks
I'm pulling a list of tables whose Statistics were last updated before a certain date. I then want to update the stats on those tables one by one
Is this possible without a cursor?
I have used cursors to solve a lot of my T-SQL problems and the only reason for that is because I dont know any other way to do it.
I want to be able to avoid cursors wherever I can.
I've always thought that a While loop can only be used in a cursor
Thanks
July 15, 2012 at 11:11 am
1.
Insert all the row into one temporary table as mentione abone in another post.
should keep the identity column in the #T table
2. Now start a while loop and loop through each of the row as below,
Declare @max_row int ,
@row int = 1
--get the total rows in the #T table.
select @max_row = Max(id) from #T
While (@row <= @max_row )
Begin
-- get the row details from the #T table.
--
Select @x = <all the required values >,
@y = <some other value>
from #T where id = @row
-- do whatever processing do you want with this result set
set @row = @row + 1
End
Is that you want?
Thanks.
Reji PR,
Bangalore
😀
July 15, 2012 at 1:02 pm
SQLSACT (7/15/2012)
Lynn Pettis (7/14/2012)
Not knowing what it is you are doing, using a cursor may be the correct thing to do. They do make sense for maintenance rountines and that sort of processing. I have used cursors and/or while loops to perform index maintenance on tables.Thanks
I'm pulling a list of tables whose Statistics were last updated before a certain date. I then want to update the stats on those tables one by one
Is this possible without a cursor?
I have used cursors to solve a lot of my T-SQL problems and the only reason for that is because I dont know any other way to do it.
I want to be able to avoid cursors wherever I can.
I've always thought that a While loop can only be used in a cursor
Thanks
Yes, you should try to avoid cursors in most of your work, However, for maintenance tasks like this one using a cursor (or a while loop with no cursor) is a viable solution.
July 15, 2012 at 10:32 pm
The following code provides you with a cursor, while loop, and a set-based solution. Depending on the level of control you need will determine which you use.
Also, I changed the base query to use the system views instead of the SQL Server 2000 compatibility views you used in your query.
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare StatsBuild cursor for
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
open StatsBuild;
declare @SchemaName sysname,
@TableName sysname,
@IndexName sysname;
fetch next from StatsBuild
into @SchemaName, @TableName, @IndexName;
while @@fetch_status = 0
begin
exec('update statistics ' + @SchemaName + '.' + @TableName + ' ' + @IndexName + ' with fullscan;');
fetch next from StatsBuild
into @SchemaName, @TableName, @IndexName;
end;
close StatsBuild;
deallocate StatsBuild;
go
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare @RecCnt int;
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
IDENTITY (int, 1, 1) StatBuildID,
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
into
#StatBuild
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810';
set @RecCnt = @@rowcount;
declare @LoopCnt int = 0;
declare @SchemaName sysname,
@TableName sysname,
@IndexName sysname;
while @LoopCnt < @RecCnt
begin
set @LoopCnt = @LoopCnt + 1;
select @SchemaName = SchemaName, @TableName = TableName, @IndexName = IndexName from #StatBuild where StatBuildID = @LoopCnt;
exec('update statistics ' + @SchemaName + '.' + @TableName + ' ' + @IndexName + ' with fullscan;');
end
go
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare @SQLCmd varchar(max);
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
), BuildStats as(
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
)
select
@SQLCmd = (select coalesce(@SQLCmd,'') + 'update statistics ' + SchemaName + '.' + TableName + ' ' + IndexName + ' with fullscan;' + char(13) + char(10)
from
BuildStats
order by
RowCnt desc,
TableName,
IndexId
for xml path(''),type).value('.','varchar(max)');
print @SQLCmd;
exec (@SQLCmd);
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
July 15, 2012 at 11:06 pm
thanks for the post
July 16, 2012 at 8:46 am
Lynn Pettis (7/15/2012)
The following code provides you with a cursor, while loop, and a set-based solution. Depending on the level of control you need will determine which you use.Also, I changed the base query to use the system views instead of the SQL Server 2000 compatibility views you used in your query.
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare StatsBuild cursor for
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
open StatsBuild;
declare @SchemaName sysname,
@TableName sysname,
@IndexName sysname;
fetch next from StatsBuild
into @SchemaName, @TableName, @IndexName;
while @@fetch_status = 0
begin
exec('update statistics ' + @SchemaName + '.' + @TableName + ' ' + @IndexName + ' with fullscan;');
fetch next from StatsBuild
into @SchemaName, @TableName, @IndexName;
end;
close StatsBuild;
deallocate StatsBuild;
go
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare @RecCnt int;
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
IDENTITY (int, 1, 1) StatBuildID,
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
into
#StatBuild
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810';
set @RecCnt = @@rowcount;
declare @LoopCnt int = 0;
declare @SchemaName sysname,
@TableName sysname,
@IndexName sysname;
while @LoopCnt < @RecCnt
begin
set @LoopCnt = @LoopCnt + 1;
select @SchemaName = SchemaName, @TableName = TableName, @IndexName = IndexName from #StatBuild where StatBuildID = @LoopCnt;
exec('update statistics ' + @SchemaName + '.' + @TableName + ' ' + @IndexName + ' with fullscan;');
end
go
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare @SQLCmd varchar(max);
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
), BuildStats as(
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
)
select
@SQLCmd = (select coalesce(@SQLCmd,'') + 'update statistics ' + SchemaName + '.' + TableName + ' ' + IndexName + ' with fullscan;' + char(13) + char(10)
from
BuildStats
order by
RowCnt desc,
TableName,
IndexId
for xml path(''),type).value('.','varchar(max)');
print @SQLCmd;
exec (@SQLCmd);
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
Thanks
After reading through this code,
I've got a lot of research, testing and learning to do !!
July 16, 2012 at 8:55 am
SQLSACT (7/16/2012)
Lynn Pettis (7/15/2012)
The following code provides you with a cursor, while loop, and a set-based solution. Depending on the level of control you need will determine which you use.Also, I changed the base query to use the system views instead of the SQL Server 2000 compatibility views you used in your query.
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare StatsBuild cursor for
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
open StatsBuild;
declare @SchemaName sysname,
@TableName sysname,
@IndexName sysname;
fetch next from StatsBuild
into @SchemaName, @TableName, @IndexName;
while @@fetch_status = 0
begin
exec('update statistics ' + @SchemaName + '.' + @TableName + ' ' + @IndexName + ' with fullscan;');
fetch next from StatsBuild
into @SchemaName, @TableName, @IndexName;
end;
close StatsBuild;
deallocate StatsBuild;
go
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare @RecCnt int;
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
IDENTITY (int, 1, 1) StatBuildID,
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
into
#StatBuild
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810';
set @RecCnt = @@rowcount;
declare @LoopCnt int = 0;
declare @SchemaName sysname,
@TableName sysname,
@IndexName sysname;
while @LoopCnt < @RecCnt
begin
set @LoopCnt = @LoopCnt + 1;
select @SchemaName = SchemaName, @TableName = TableName, @IndexName = IndexName from #StatBuild where StatBuildID = @LoopCnt;
exec('update statistics ' + @SchemaName + '.' + @TableName + ' ' + @IndexName + ' with fullscan;');
end
go
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare @SQLCmd varchar(max);
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
), BuildStats as(
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
)
select
@SQLCmd = (select coalesce(@SQLCmd,'') + 'update statistics ' + SchemaName + '.' + TableName + ' ' + IndexName + ' with fullscan;' + char(13) + char(10)
from
BuildStats
order by
RowCnt desc,
TableName,
IndexId
for xml path(''),type).value('.','varchar(max)');
print @SQLCmd;
exec (@SQLCmd);
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
Thanks
After reading through this code,
I've got a lot of research, testing and learning to do !!
Any questions, be sure to ask.
July 16, 2012 at 8:59 am
Lynn Pettis (7/16/2012)
SQLSACT (7/16/2012)
Lynn Pettis (7/15/2012)
The following code provides you with a cursor, while loop, and a set-based solution. Depending on the level of control you need will determine which you use.Also, I changed the base query to use the system views instead of the SQL Server 2000 compatibility views you used in your query.
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare StatsBuild cursor for
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
open StatsBuild;
declare @SchemaName sysname,
@TableName sysname,
@IndexName sysname;
fetch next from StatsBuild
into @SchemaName, @TableName, @IndexName;
while @@fetch_status = 0
begin
exec('update statistics ' + @SchemaName + '.' + @TableName + ' ' + @IndexName + ' with fullscan;');
fetch next from StatsBuild
into @SchemaName, @TableName, @IndexName;
end;
close StatsBuild;
deallocate StatsBuild;
go
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare @RecCnt int;
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
IDENTITY (int, 1, 1) StatBuildID,
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
into
#StatBuild
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810';
set @RecCnt = @@rowcount;
declare @LoopCnt int = 0;
declare @SchemaName sysname,
@TableName sysname,
@IndexName sysname;
while @LoopCnt < @RecCnt
begin
set @LoopCnt = @LoopCnt + 1;
select @SchemaName = SchemaName, @TableName = TableName, @IndexName = IndexName from #StatBuild where StatBuildID = @LoopCnt;
exec('update statistics ' + @SchemaName + '.' + @TableName + ' ' + @IndexName + ' with fullscan;');
end
go
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
declare @SQLCmd varchar(max);
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
), BuildStats as(
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
)
select
@SQLCmd = (select coalesce(@SQLCmd,'') + 'update statistics ' + SchemaName + '.' + TableName + ' ' + IndexName + ' with fullscan;' + char(13) + char(10)
from
BuildStats
order by
RowCnt desc,
TableName,
IndexId
for xml path(''),type).value('.','varchar(max)');
print @SQLCmd;
exec (@SQLCmd);
with GrpPartitions as (
select
par.object_id,
par.index_id,
sum(par.rows) as RowCnt
from
sys.partitions par
group by
par.object_id,
par.index_id
)
select
schema_name(tab.schema_id) as SchemaName,
tab.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
par.RowCnt,
STATS_DATE(ind.object_id,ind.index_id) as StatsDate
from
sys.tables tab
inner join sys.indexes ind
on (tab.object_id = ind.object_id)
inner join GrpPartitions par
on (ind.object_id = par.object_id
and ind.index_id = par.index_id)
where
STATS_DATE(ind.object_id,ind.index_id) < '20120810'
order by
par.RowCnt desc,
tab.name,
ind.index_id;
go
Thanks
After reading through this code,
I've got a lot of research, testing and learning to do !!
Any questions, be sure to ask.
Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply