February 1, 2009 at 12:52 am
I use this code to analyze my DB and depend on the value in the columns I devided to three categories Lookup, single value and normal
the fiirst table (Basic_Statistics)I use to insert the column name and table name and the type
the second table (Secnd_Statistics) which contain the Basic_Statistics ID and the vlues appeared if the code is single or look up
the code insert well in Basic_Statistics table
but not well in the Secnd_Statistics table
the code is
-------------- Create the basic tble---------------
IF OBJECT_ID ('dbo.Basic_Statistics','U') IS NULL
create table dbo.Basic_Statistics
(
BS_ID int identity(1,1),
BS_TableName varchar(100),
BS_ColumnName varchar(100),
BS_Type varchar(100)
)
-------------------- Create secondroy statistics
IF OBJECT_ID ('dbo.Secnd_Statistics','U') IS NULL
create table dbo.Secnd_Statistics
(
Sec_BS_ID int ,
Sec_Value text
)
set nocount on
IF OBJECT_ID('TempDB..#spaceused','U') IS NULL
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 [name] not like 'DEL_%' and [rows] !=0 and len(name)=4
open RENAMETABLE
DECLARE @@RTABLE varchar(128)
DECLARE @@RTABLEnew varchar(128)
FETCH NEXT FROM RENAMETABLE INTO @@RTABLE
WHILE @@FETCH_STATUS = 0
BEGIN
IF OBJECT_ID('TempDB..#DISTINCTC','U') IS NULL
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 @row int
declare @y nvarchar(120)
declare @SQLString nvarchar(4000)
declare @SQLString1 nvarchar(4000)
fetch next from Dist into @y
while @@fetch_status=0
begin
---------- Preparing variables for statistics
SET @SQLString = N'select @n=count(distinct('+@y+N')) from '+@@RTABLE+''
EXEC sp_executesql @SQLString, N'@n int OUTPUT', @z OUTPUT
print @z
SET @SQLString1 = N'select @w=count(*) from '+@@RTABLE+''
EXEC sp_executesql @SQLString1, N'@w int OUTPUT', @row OUTPUT
print @row
--------The table that will carry the distinct value
IF OBJECT_ID('TempDB..#DISTINCTC1','U') IS NULL
create table #DISTINCTC1( DistinctVal varchar(4000))
insert into #DISTINCTC1 EXEC('select distinct '+@y+' from '+@@RTABLE+'')
--select * from #DISTINCTC1
if (@z=1)
begin
--insert into Basic_Statistics values(@@RTABLE,@y,'Single Value')
insert into Secnd_Statistics select max(BS_ID), convert(varchar(4000),DistinctVal) from Basic_Statistics, #DISTINCTC1 group by convert(varchar(4000),DistinctVal)
end
else if ( ((@row/10) > 20 and @z <=20) or ((@row/10)<=20 and @z < = (@row/10)))
begin
insert into Basic_Statistics values(@@RTABLE,@y,'Look Up')
declare Insertion cursor for select cast(DistinctVal as varbinary) from #DISTINCTC1
open Insertion
Declare @I varbinary
FETCH NEXT FROM Insertion INTO @I
WHILE @@FETCH_STATUS = 0
begin
--print @@RTABLE
--print @y
--print @I
insert into Secnd_Statistics select max(BS_ID), convert(varchar(4000),@I) from Basic_Statistics
FETCH NEXT FROM INSERTION INTO @I
END
CLOSE INSERTION
DEALLOCATE INSERTION
end
else
insert into Basic_Statistics values(@@RTABLE,@y,'Normal')
--drop table #DISTINCTC1
fetch next from Dist into @y
end
close Dist
Deallocate Dist
drop table #DISTINCTC
FETCH NEXT FROM RENAMETABLE INTO @@RTABLE
END
CLOSE RENAMETABLE
DEALLOCATE RENAMETABLE
drop table #spaceused
February 1, 2009 at 12:40 pm
ali.m.habib (2/1/2009)
the code insert well in Basic_Statistics tablebut not well in the Secnd_Statistics table
OK:
1) You should not be using cursors for this, and definitely not nested cursors. You should be using set-based SQL.
2) Select MAX(bs_id) is not a good way to get the identity value of the row that you just inserted. Instead, use the SCOPE_IDENTITY() function immediately after the INSERT for single row inserts, or use the OUTPUT clause to send the created ID's to another table for multi-row inserts.
3) and most importantly, "not well", above, does not really tell us anything. Please tell us what is wrong with the "not well" inserts.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply