Density Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Density Query Help

Author

Topic

ehauser0613
Starting Member

6 Posts

Posted - 2012-02-09 : 11:48:42

I am trying to run a density statement using all of the tables from the AdventureWorksDW2008R2 database. We want all of the density information to dump into a Density table in a separate Density database.

When I run the code below:

DECLARE 
      @schemaName		varchar(500),
      @dbName                   varchar(500),
      @tableName		varchar(500),
      @columnName		varchar(500),
      @columnDataType	        varchar(500),
      @columnLength		bigint,
      @sqlStatement		varchar(8000),
      @sqlStatementIdx	        varchar(1000),
      @rundate                  varchar(20)
            
-- Modify the following value based upon the source database name 
      --set @dbName       = 'AdventureWorksDW2008R2'
      set @dbName       = 'AdventureWorksDW2008R2'
      set @schemaName   = 'dbo'
      set @tableName    = ''
      set @columnName   = ''
      set @sqlStatement = ''
      set @rundate      = CONVERT(VARCHAR(20), GETDATE(), 100)

-- Use this SQL statement to truncate all rows in the dbo.DensityTable, if refreshing the density information 
-- TRUNCATE TABLE dbo.DensityTable; 

DECLARE db_cursor CURSOR FOR  

select distinct 
       a.table_name as "Table Name",
       b.column_name,
       b.DATA_TYPE,
       isnull(b.CHARACTER_MAXIMUM_LENGTH,0),
       'select distinct ''' +  a.table_name + ''' as TableName,
       ''' + b.column_name + ''' as ColumnName, 
       ''' + b.DATA_TYPE + ''' as datatype, 
       ''' + case when b.DATA_TYPE not in ('date','datetime','smalldatetime','bit','float','real') 
                  then isnull(cast(b.CHARACTER_MAXIMUM_LENGTH as varchar), cast(b.NUMERIC_PRECISION as varchar) +','+ cast(b.NUMERIC_SCALE as varchar)) 
                  else ''
             end + ''' as data_length, 
       ''' + b.IS_NULLABLE + ''' as nullable,
       (select count(*) from ' + @schemaName + '.[' + a.table_name + ']) as TotalRows,
       (select count(*) from ' + @schemaName + '.[' + a.table_name + '] where [' + b.column_name + '] is null) as NullRows,
       (select count(*) from ' + @schemaName + '.[' + a.table_name + '] where [' + b.column_name + '] like ' + '''' + ' %' + '''' + ') as Val_is_Space, ' + 

       case when b.DATA_TYPE not in ('varchar','char','nvarchar','nchar','text','ntext','date','datetime','smalldatetime') 
            then '(select count(*) from ' + @schemaName + '.[' + a.table_name + '] where [' + b.column_name + '] = 0) as Val_is_Zero, ' 
            when b.DATA_TYPE in ('text','ntext','date','datetime','smalldatetime') 
            then '''''' + ' as Val_is_Zero, ' 
            when b.DATA_TYPE in ('varchar','char','nvarchar','nchar') 
            then '(select count(*) from ' + @schemaName + '.[' + a.table_name + '] where [' + b.column_name + '] = '''+ '0' + ''') as Val_is_Zero, ' 
       end + 
       case when b.DATA_TYPE in ('text','ntext') 
            then '''''' + ' as DistinctVal, ' 
            else '(select count(distinct [' + b.column_name + ']) from ' + @schemaName + '.[' + a.table_name + ']) as DistinctVal, '
       end + 
       case when b.DATA_TYPE in ('text','ntext','bit') 
            then '''''' + ' as MinVal, ' 
            else '(select min([' + b.column_name + ']) from ' + @schemaName + '.[' + a.table_name + ']) as MinVal, '
       end + 
       case when b.DATA_TYPE in ('text','ntext','bit') 
            then '''''' + ' as NumMinVal, ' 
            else '(select count(*) from ' + @schemaName + '.[' + a.table_name + '] where [' + b.column_name + '] = (select min([' + b.column_name + ']) from ' + @schemaName + '.[' + a.table_name + '])) as NumMinVal, ' 
       end + 
       case when b.DATA_TYPE in ('text','ntext','bit') 
            then '''''' + ' as MaxVal, ' 
            else '(select max([' + b.column_name + ']) from ' + @schemaName + '.[' + a.table_name + ']) as MaxVal, '
       end + 
       case when b.DATA_TYPE in ('text','ntext','bit') 
            then '''''' + ' as NumMaxVal, ' 
            else '(select count(*) from ' + @schemaName + '.[' + a.table_name + '] where [' + b.column_name + '] = (select max([' + b.column_name + ']) from ' + @schemaName + '.[' + a.table_name + '])) as NumMaxVal, ' 
       end + 
       case when b.DATA_TYPE not in ('varchar','char','nvarchar','nchar','text','ntext','date','datetime','smalldatetime','bit') 
            then '(select avg(cast([' + b.column_name + ']as bigint)) from ' + @schemaName + '.[' + a.table_name + ']) as Avg_Val, '

 else '''''' + ' as Avg_Val,'
       end +
       case when b.DATA_TYPE not in ('varchar','char','nvarchar','nchar','text','ntext','date','datetime','smalldatetime','bit') 
            then '(select count(*) from ' + @schemaName + '.[' + a.table_name + '] where [' + b.column_name + '] = (select avg(cast([' + b.column_name + ']as bigint)) from ' + @schemaName + '.[' + a.table_name + '])) as NumAvgVal, '
            else '''''' + ' as NumAvgVal,'
       end +
     ' (select min(datalength([' + b.column_name + '])) from ' + @schemaName + '.[' + a.table_name + ']) as Min_Length,
       (select count(*) from ' + @schemaName + '.[' + a.table_name + '] where datalength([' + b.column_name + ']) = (select min(datalength([' + b.column_name + '])) from ' + @schemaName + '.[' + a.table_name + '])) as Num_Min_Length,
       (select max(datalength([' + b.column_name + '])) from ' + @schemaName + '.[' + a.table_name + ']) as Max_Length,
       (select count(*) from ' + @schemaName + '.[' + a.table_name + '] where datalength([' + b.column_name + ']) = (select max(datalength([' + b.column_name + '])) from ' + @schemaName + '.[' + a.table_name + '])) as Num_Max_Length,
       (select avg(datalength([' + b.column_name + '])) from ' + @schemaName + '.[' + a.table_name + ']) as Avg_Length, 
       (select count(*) from ' + @schemaName + '.[' + a.table_name + '] where datalength([' + b.column_name + ']) = (select avg(datalength([' + b.column_name + '])) from ' + @schemaName + '.[' + a.table_name + '])) as Num_Avg_Length,' +
       '''' + @rundate + '''' + ' as rundate, 
       ''' + a.TABLE_CATALOG + ''' as database_nm,
       ''' + a.table_schema + ''' as schema_nm 
       from AdventureWorksDW2008R2.INFORMATION_SCHEMA.TABLES where table_schema = ' + '''' + @schemaName + '''' +  ' and table_name = ' + '''' + a.table_name + ''';'
from AdventureWorksDW2008R2.INFORMATION_SCHEMA.TABLES a 
inner join AdventureWorksDW2008R2.INFORMATION_SCHEMA.COLUMNS b on a.table_name = b.table_name and a.table_schema = b.table_schema
where a.TABLE_CATALOG = @dbName
and   a.table_schema  = @schemaName
--and   a.table_name in (select Table_Name from dbo.DensityTable_List where Density_Status = 'Okay')
--and   a.table_name in (select Table_Name from AdventureWorksDW2008R2.INFORMATION_SCHEMA.Tables)
and   a.table_type = 'BASE TABLE'
                                                                                                                                                                                                          
OPEN db_cursor   
FETCH NEXT FROM db_cursor 
INTO
      @tableName,
      @columnName, 
      @columnDataType,
      @columnLength,
      @sqlStatement  

 
WHILE @@FETCH_STATUS = 0   
BEGIN  
     --print (@sqlStatement) 
     set @sqlStatementIdx = 'CREATE NONCLUSTERED INDEX [IX_Density_' + @columnName + '] ON ' + @schemaName + '.[' + @tableName + '] ([' + @columnName + '] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
	 if @columnDataType <> 'text' and @columnDataType <> 'image' and @columnLength < 900 and @columnLength <> -1 exec (@sqlStatementIdx)

       INSERT INTO Density.dbo.DensityTable
      (Table_Name,
        Column_Name,
        Datatype,
        Data_length,
        Nullable,
        Total_Rows,
        Null_Rows,
        Blank_Rows,
        Zero_Rows,
        Distinct_Val,
        Min_Val,
        Num_Min_Val,
        Max_Val,
        Num_Max_Val,
        Avg_Val,
        Num_Avg_Val,
        Min_Length,
        Num_Min_Length,
        Max_Length,
        Num_Max_Length,
        Avg_Length,
        Num_Avg_Length,
        rundate,
        database_nm,
        schema_nm)
      exec (@sqlStatement)

     set @sqlStatementIdx = 'drop INDEX [IX_Density_' + @columnName + '] ON ' + @schemaName + '.[' + @tableName + ']'
	 if @columnDataType <> 'text' and @columnDataType <> 'image' and @columnLength < 900 and @columnLength <> -1 exec (@sqlStatementIdx)

FETCH NEXT FROM db_cursor INTO      
      @tableName,
      @columnName, 
      @columnDataType,
      @columnLength,
      @sqlStatement    

END   

CLOSE db_cursor   
DEALLOCATE db_cursor

It comes up with this error:

Msg 1088, Level 16, State 12, Line 1
Cannot find the object "dbo.AdventureWorksDWBuildVersion" because it does not exist or you do not have permissions.

Can someone point me in the right direction please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-09 : 14:15:16

I dont think error is in posted code as I cant find out dbo.AdventureWorksDWBuildVersion table referred in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ehauser0613
Starting Member

6 Posts

Posted - 2012-02-09 : 14:38:51

Thanks. I finally got all points in the script to point to the correct places. I was just wondering more so if anyone had any better ideas on how to accomplish the same thing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-09 : 17:53:14

sorry...didnt get that. How do you expect someone to help you out without knowing what real issue is?
The posted code is not the cause of your problem so unless you show full code or explain the real problem i dont think anybody can guess and help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ehauser0613
Starting Member

6 Posts

Posted - 2012-02-10 : 00:03:01

I see your point. That is the full code, but I guess I failed to fully explain the issue. I do appreciate your time and sorry for the confusion. This issue has been resolved.

Thanks again...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:27:32

quote:
Originally posted by ehauser0613

I see your point. That is the full code, but I guess I failed to fully explain the issue. I do appreciate your time and sorry for the confusion. This issue has been resolved.

Thanks again...

for the sake of others looking into thread can you tell how it got resolved?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources