January 29, 2009 at 12:28 am
the code not display errors except when running which are :
Line 1: Incorrect syntax near '~'. while the code not have any ~ in it
Could not complete cursor operation because the table schema changed after the cursor was declared.
the code is :
set nocount on
create table #spaceused (
name nvarchar(120),
rows char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
declare Tables cursor for
select name
from sysobjects where type='U' --> stands for user table
order by name asc
OPEN Tables
DECLARE @table varchar(128)
FETCH NEXT FROM Tables INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #spaceused exec sp_spaceused @table
FETCH NEXT FROM Tables INTO @table
END
CLOSE Tables
DEALLOCATE Tables
select * from #spaceused
--drop table #spaceused
declare RENAMETABLE cursor for
select [name] from #spaceused where rows=0
open RENAMETABLE
DECLARE @@RTABLE varchar(128)
DECLARE @@RTABLEnew varchar(128)
FETCH NEXT FROM RENAMETABLE INTO @@RTABLE
WHILE @@FETCH_STATUS = 0
BEGIN
create table #DISTINCTC(
[name] nvarchar(120)
)
insert into #DISTINCTC SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = @@RTABLE
------------------------
--select * from #DISTINCTC
declare Dist cursor for
select [name]from #DISTINCTC
open Dist
DECLARE @z int
declare @y nvarchar(120)
declare @SQLString nvarchar(4000)
fetch next from Dist into @y
while @@fetch_status=0
begin
SET @SQLString = N'select @n=count(distinct('+@y+N')) from '+@@RTABLE+''
EXEC sp_executesql @SQLString, N'@n int OUTPUT', @z OUTPUT
if ((select @z)<= 10)
begin
create table #DISTINCTC1( DistinctVal varbinary(4000))
insert into #DISTINCTC1 EXEC('select distinct '+@y+' from '+@@RTABLE+'')
select * from #DISTINCTC1
--declare Insertion cursor for select * from #DISTINCTC1
--open Insertion
--Declare @I varbinary(4000)
--FETCH NEXT FROM Insertion INTO @I
--WHILE @@FETCH_STATUS = 0
--begin
insert into SATIS select @@RTABLE,@y,DistinctVal from #DISTINCTC1
--FETCH NEXT FROM Insertion INTO @I
--end
--close Insertion
--Deallocate Insertion
end
drop table #DISTINCTC1
fetch next from Dist into @y
end
close Dist
Deallocate Dist
drop table #DISTINCTC
drop table #spaceused
FETCH NEXT FROM RENAMETABLE INTO @@RTABLE
END
CLOSE RENAMETABLE
DEALLOCATE RENAMETABLE
select * from SATIS
--delete SATIS
any suggestion please
January 29, 2009 at 2:10 am
I found the solution thnax for all the member
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply