Please take a look at the code and help me on the last part --put all together = fail !!!
I wanted to generate a string for executing sp_executesql but I'm getting an error at the end
I also tried open cursor but it error out too
basically I'm trying to get actual row count per column per table for all tables
because general row count want give you if new column added and populated
Thanks,
Ed Dror
USE AdventureWorks2014
GO
-- Create view to hold the dataset
Alter view vColumnSchema
AS
with myColumnName
As
(
Select TOP (100) PERCENT
GetDate() As create_date,
@@SERVERNAME As Server_Name,
DB_Name() AS database_name,
c.[object_id],
s.name AS [schema_name],
t.name as table_name,
c.name as column_name,
p.rows AS NUM_ROWS,
c.[precision]
from sys.tables as t
INNER JOIN sys.columns as c with(nolock) on t.[object_id] = c.[object_id]
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.name NOT LIKE 'dt%'
AND i.object_id > 255
AND i.index_id <= 1
And s.name is not null
Order by s.name,
t.name
)
Select Top 100 percent
create_date,
Server_Name,
database_name,
[schema_name],
table_name,
column_name,
NUM_ROWS,
case when precision = 0 then
'Select Count('+column_name+')'+' from ' +[schema_name]+'.'+table_name+ ' where '+column_name+'<> '''' '
else
'Select Count(' +column_name+')'+' from ' +[schema_name]+ '.'+table_name+' where '+column_name+'> 0'
END as ColumnCount,
case when precision = 0
then ' <> '''' '
Else '> 0'
END As WhereClause
from myColumnName
Order by [schema_name],
table_name
GO
select * from vColumnSchema
where table_name = 'ProductCostHistory'
go
select * from Production.ProductCostHistory -- 395 records
--pick one record and test it = pass
Select Count(EndDate) from Production.ProductCostHistory where EndDate<> '' --200 records
GO
--test = pass on both whereClause
Declare @Column_name nvarchar(50) = 'EndDate'
Declare @Schema_Name nvarchar(20) = 'Production'
declare @table_name nvarchar(50) = 'ProductCostHistory'
declare @whereClause nvarchar(5) = '<> '''' '
declare @sqltext nvarchar(max) ='
select count('+@Column_name+') as ColumnCount from ' +@Schema_Name+'.'+@table_name+' where '+@Column_name+' '+@WhereClause+'
'
EXECUTE sp_executesql @sqltext
GO
--examine each column = pass
Declare @Column_name nvarchar(50) = 'select Column_name from vColumnSchema'
EXECUTE sp_executesql @Column_name
GO
Declare @schema_name nvarchar(50) = 'select schema_name from vColumnSchema'
EXECUTE sp_executesql @schema_name
GO
declare @table_name nvarchar(50) = 'select table_name from vColumnSchema'
EXECUTE sp_executesql @table_name
GO
declare @WhereClause nvarchar(50) = 'select WhereClause from vColumnSchema'
EXECUTE sp_executesql @WhereClause
GO
--put all together = fail !!!
declare @Column_name nvarchar(50) = 'select Column_name from vColumnSchema'
declare @schema_name nvarchar(50) = 'select schema_name from vColumnSchema'
declare @table_name nvarchar(50) = 'select table_name from vColumnSchema'
declare @WhereClause nvarchar(5) = 'select WhereClause from vColumnSchema';
declare @sqltext nvarchar(max) ='
select count('+@Column_name+') as ColumnCount from ' +@Schema_Name+'.'+@table_name+' where '+@Column_name+' '+@WhereClause+'
'
EXECUTE sp_executesql @sqltext
go
