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

quote:

---

Originally posted by Peso

Which line produces the error?
Is it this line = "insert into #spaceused exec sp_spaceused @table" ?

---

E 12°55'05.63"
N 56°04'39.26"

---

Msg 16915, Level 16, State 1, Line 53
A cursor with the name 'Dist' already exists.
Msg 16905, Level 16, State 1, Line 55
The cursor is already open.
Msg 16943, Level 16, State 4, Line 59
Could not complete cursor operation because the table schema changed after the cursor was declared.
Msg 16943, Level 16, State 4, Line 91
Could not complete cursor operation because the table schema changed after the cursor was declared.
Msg 3701, Level 11, State 5, Line 98
Cannot drop the table '#spaceused', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 99
Cannot drop the table '#renametable', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 100
Cannot drop the table '#distinctc', because it does not exist or you do not have permission.

quote:

---

Originally posted by Peso

Also, first you use sp_spaceused to get all tables in current database.
Then you create a cursor for those tables having rowcount equal to zero.
For those records you count the distinct values.

Which values? There are no records in the table!

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by Ali.M.Habib

thanx for the attention I solve d the problem

---