Gav B (6/8/2010)
Gaby - I have a question about your script.What is the purpose of of the line which says:
[font="System"]delete #findAccessTimes where rownum = @ctr[/font]
It seems to work just fine with or without it?
- Gav B
Oops, my bad...buggy code. As there is never a guarantee on which row is selected without an expressed WHERE clause, I should have caught that. Error fixed and submitted for revision in main body of article.
Try this:
set nocount on
create table #findAccessTimes (rownum int identity(1,1), command varchar(8000))
insert into #findAccessTimes(command)
select 'select top 1 ''' + so.name + ''' [TableName], [' + sc.name + '] from ['
+ so.name + '] (nolock) order by [' + sc.name + '] desc' from syscolumns sc
inner join sysobjects so on so.id = sc.id
where so.type = 'U' and sc.xtype = 61
declare @query varchar(8000),
@ctr int,
@numrows int
select @numrows = count(*) from #findAccessTimes
set @ctr = 1
while (@ctr <= @numrows)
begin
select @query = command from #findAccessTimes where rownum = @ctr -- Edit here
print(@query) -- Verify with this print to make sure you like the queries
-- exec(@query) -- that are generated before actually running them.
delete #findAccessTimes where rownum = @ctr
set @ctr = @ctr + 1
end
go
drop table #findAccessTimes
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein