August 12, 2015 at 7:38 pm
Hi there,
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
August 12, 2015 at 11:27 pm
SQL is not an interpreted language so will not work the way you are thinking right now.
Here is a quick demo using your view that does work although the resultsets are almost certainly not shaped the way you are wanting them:
DECLARE @sql NVARCHAR(MAX) = N'UNION ALL ';
SELECT @sql += 'select ''' + database_name + '.' + schema_name + '.' + table_name + '.' +
column_name + ''' as column_name, (' + ColumnCount + ') as count '
FROM dbo.vColumnSchema;
SET @sql = STUFF(@sql, 1, 10, N'');
EXEC (@sql);
If you provide a sample of your desired resultset I can help you go further with the solution.
Note: the TOP 100 PERCENT and ORDER BY in your VIEW should be removed as they do not prove anything. any ordering you might be seeing is anecdotal. if you want something ordered you need to add an ORDER BY clause when you select from the VIEW not in the definition of it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 13, 2015 at 6:49 pm
Thank you for your replay but this give me an error
Thanks,
Oded Dror
August 13, 2015 at 7:30 pm
Ok. What error?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 13, 2015 at 9:06 pm
This is he error I got
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Schema'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Database'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Primary'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
August 13, 2015 at 10:33 pm
My code does not mention those values so chances are you have them in your data being returned by your view. make sure your view is using QUOTENAME where appropriate, e.g. this
'Select Count(' + column_name + ')' + ' from ' + [schema_name] + '.' + table_name + ' where ' + column_name + '<> '''' '
should probably look more like this
'Select Count(' + QUOTENAME(column_name) + ')' + ' from ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ' where ' + QUOTENAME(column_name) + '<> '''' '
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 13, 2015 at 11:16 pm
What exactly are you trying to accomplish with this code? What are the expected results?
August 13, 2015 at 11:33 pm
Lynn Pettis (8/13/2015)
What exactly are you trying to accomplish with this code? What are the expected results?
this is what I am working off:
basically I'm trying to get actual row count per column per table for all tables
Looking at the OPs VIEW code he means "excluding NULL or empty values" per column.
I am looking at it as an exercise in data profiling.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 14, 2015 at 2:54 am
Here is a simple profiling procedure I've used for years, nothing fancy but often quite helpful.
😎
NOTE: By the nature of the profiling, it can and will place considerable stress on the server, specially when profiling large tables with many columns.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**************************************************************************
Eirikur Eiriksson
---------------------------------------------------------------
Name: dbo.EE_PROFILE_TABLE
Description: Quick Data Profiling (The Disco version)
Usage: EXECUTE dbo.EE_PROFILE_TABLE ,[SchemaName],[TableName]
---------------------------------------------------------------
Output:
[SchemaName].[TableName]: Column Name
IS_NULLABLE : YES if nullable
DATA_TYPE : Data type name
MAX_LENGTH : Specified length if applicable
PROPERTY : IDENTITY,ROWGUIDCOL,COMPUTED,FILESTREAM,XML_DOCUMENT,ASSEMBLY_TYPE,NORMAL
INDEX_COL : IDEXED,IDEXED (Included),(Included),NONE
DFLTVAL : Default value
NCOUNT : Null count
NNCOUNT : Non-null count
DSCOUNT : Distinct Count
DLMCOUNT : Delimiter character count
TQLCOUNT : Text qualifier count
LTZCOUNT : Less than zero count
EQZCOUNT : Equal to zero count
MAXVAL : Maximum value
MINVAL : Minimum
TXTMXLEN : Maximum text length
TXTMNLEN : Minimum text length
**************************************************************************/
CREATE PROCEDURE [dbo].[EE_PROFILE_TABLE]
(
@SCHEMNVARCHAR(32), @TABLENVARCHAR(128)
)
AS
DECLARE @SQLSTR NVARCHAR(MAX) = N''
DECLARE @NL NVARCHAR(2) = NCHAR(13) + NCHAR(10)
DECLARE @T NVARCHAR(1) = NCHAR(9)
DECLARE @TPRFX NVARCHAR(128) = N'IDXVW_Stg_'
DECLARE @DELIM NVARCHAR(2) = N','
DECLARE @TXQFR NVARCHAR(2) = N'"'
DECLARE @SQ NVARCHAR(1) = N''
DECLARE @TBL_NAME NVARCHAR(255) = N''
SELECT @TBL_NAME = @SCHEM + N'.' + @TABLE
SELECT @SQLSTR = @SQLSTR + N'SELECT ''' + ISC.COLUMN_NAME + N''' AS ['+@TBL_NAME + N']
, (SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @TABLE + N''' AND COLUMN_NAME = ''' + ISC.COLUMN_NAME + N''') AS IS_NULLABLE
, (SELECT UPPER(DATA_TYPE) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @TABLE + N''' AND COLUMN_NAME = ''' + ISC.COLUMN_NAME + N''') AS DATA_TYPE
, (SELECT max_length FROM SYS.COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N') AS MAX_LENGTH
, ''PROPERTY''=
CASE
WHEN (SELECT IS_IDENTITY FROM SYS.COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N') = 1 THEN ''IDENTITY''
WHEN (SELECT is_rowguidcol FROM SYS.COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N') = 1 THEN ''ROWGUIDCOL''
WHEN (SELECT is_computed FROM SYS.COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N') = 1 THEN ''COMPUTED''
WHEN (SELECT is_filestream FROM SYS.COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N') = 1 THEN ''FILESTREAM''
WHEN (SELECT is_xml_document FROM SYS.COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N') = 1 THEN ''XML_DOCUMENT''
WHEN (SELECT is_assembly_type FROM SYS.TYPES WHERE NAME = (SELECT UPPER(DATA_TYPE) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @TABLE + N''' AND COLUMN_NAME = ''' + ISC.COLUMN_NAME + N''')) = 1 THEN ''ASSEMBLY_TYPE''
ELSE ''NORMAL''
END
, ''INDEX_COL''=
CASE
WHEN ((SELECT COUNT(object_id) FROM SYS.INDEX_COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N') = 1 AND (SELECT COUNT(is_included_column) FROM SYS.INDEX_COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N' AND is_included_column = 1) > 0)THEN ''IDEXED (Included)''
WHEN (SELECT COUNT(object_id) FROM SYS.INDEX_COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N') = 1 THEN ''IDEXED''
WHEN ((SELECT COUNT(object_id) FROM SYS.INDEX_COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N') = 0 AND (SELECT COUNT(is_included_column) FROM SYS.INDEX_COLUMNS WHERE OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N' AND is_included_column = 1) > 0)THEN ''(Included)''
ELSE ''NONE''
END
, (SELECT ISNULL((SELECT DEFINITION FROM sys.default_constraints WHERE PARENT_OBJECT_ID = ' + CAST(SC.object_id AS VARCHAR(12)) + N' AND PARENT_COLUMN_ID = ' + CAST(SC.column_id AS VARCHAR(12)) + N'),'''')) AS DFLTVAL
, (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NULL) AS NCOUNT
, (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS NNCOUNT
, (SELECT COUNT(*) FROM (SELECT DISTINCT [' + ISC.COLUMN_NAME + N'] FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS A) AS DSCOUNT
, (SELECT COUNT(*) FROM (SELECT DISTINCT [' + ISC.COLUMN_NAME + N'] FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] LIKE ''%' + @DELIM + N'%'') AS A) AS DLMCOUNT
, (SELECT COUNT(*) FROM (SELECT DISTINCT [' + ISC.COLUMN_NAME + N'] FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] LIKE ''%' + @TXQFR + N'%'') AS A) AS TQLCOUNT
--,'
+ CASE ISC.DATA_TYPE
WHEN N'date'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'time'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'tinyint'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'smallint'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'int'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'smalldatetime'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'real'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'money'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'datetime'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'float'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'bit'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'decimal'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'numeric'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'smallmoney'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
WHEN N'bigint'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] > 0) AS GTZCOUNT '
ELSE N'(SELECT 0) AS GTZCOUNT '
END + N'
,' + CASE ISC.DATA_TYPE
WHEN N'time'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'tinyint'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'smallint'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'int'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'real'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'money'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'float'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'bit'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'decimal'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'numeric'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'smallmoney'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
WHEN N'bigint'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] < 0) AS LTZCOUNT '
ELSE N'(SELECT 0) AS LTZCOUNT '
END + N'
,' + CASE ISC.DATA_TYPE
WHEN N'tinyint'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
WHEN N'smallint'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
WHEN N'int'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
WHEN N'real'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
WHEN N'money'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
WHEN N'float'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
WHEN N'bit'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
WHEN N'decimal'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
WHEN N'numeric'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
WHEN N'smallmoney'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
WHEN N'bigint'THEN N' (SELECT COUNT(*) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] = 0) AS EQZCOUNT '
ELSE N'(SELECT 0) AS EQZCOUNT '
END + N'
,'
+ CASE ISC.DATA_TYPE
WHEN N'tinyint'THEN N' (CAST((SELECT MAX(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MAXVAL '
WHEN N'smallint'THEN N' (CAST((SELECT MAX(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MAXVAL '
WHEN N'int'THEN N' (CAST((SELECT MAX(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MAXVAL '
WHEN N'real'THEN N' (CAST((SELECT MAX(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MAXVAL '
WHEN N'money'THEN N' (CAST((SELECT MAX(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MAXVAL '
WHEN N'float'THEN N' (CAST((SELECT MAX(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MAXVAL '
WHEN N'decimal'THEN N' (CAST((SELECT MAX(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MAXVAL '
WHEN N'numeric'THEN N' (CAST((SELECT MAX(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MAXVAL '
WHEN N'smallmoney'THEN N' (CAST((SELECT MAX(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MAXVAL '
WHEN N'bigint'THEN N' (CAST((SELECT MAX(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MAXVAL '
ELSE N'(SELECT 0) AS MAXVAL '
END + N'
,' + CASE ISC.DATA_TYPE
WHEN N'tinyint'THEN N' (CAST((SELECT MIN(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MINVAL '
WHEN N'smallint'THEN N' (CAST((SELECT MIN(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MINVAL '
WHEN N'int'THEN N' (CAST((SELECT MIN(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MINVAL '
WHEN N'real'THEN N' (CAST((SELECT MIN(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MINVAL '
WHEN N'money'THEN N' (CAST((SELECT MIN(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MINVAL '
WHEN N'float'THEN N' (CAST((SELECT MIN(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MINVAL '
WHEN N'decimal'THEN N' (CAST((SELECT MIN(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MINVAL '
WHEN N'numeric'THEN N' (CAST((SELECT MIN(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MINVAL '
WHEN N'smallmoney'THEN N' (CAST((SELECT MIN(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MINVAL '
WHEN N'bigint'THEN N' (CAST((SELECT MIN(' + ISC.COLUMN_NAME + N') FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL) AS VARCHAR(50))) AS MINVAL '
ELSE N'(SELECT 0) AS MINVAL '
END
+ N'
, ''TXTMXLEN'' =
CASE
WHEN (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @TABLE + N''' AND COLUMN_NAME = ''' + ISC.COLUMN_NAME + N''') IS NULL THEN 0
ELSE (SELECT MAX(LEN([' + ISC.COLUMN_NAME + N'])) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL)
END
, ''TXTMNLEN'' =
CASE
WHEN (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @TABLE + N''' AND COLUMN_NAME = ''' + ISC.COLUMN_NAME + N''') IS NULL THEN 0
ELSE (SELECT MIN(LEN([' + ISC.COLUMN_NAME + N'])) FROM '+@TBL_NAME + N' WHERE [' + ISC.COLUMN_NAME + N'] IS NOT NULL)
FROM INFORMATION_SCHEMA.COLUMNS ISC
INNER JOIN sys.columns SC
ON object_id(ISC.TABLE_SCHEMA + '.' + ISC.TABLE_NAME) = SC.object_id
and ISC.COLUMN_NAME = SC.name
WHERE ISC.TABLE_NAME = @TABLE
AND ISC.TABLE_SCHEMA = @SCHEM
ORDER BY ISC.ORDINAL_POSITION
SELECT @SQLSTR = SUBSTRING(@SQLSTR,1,(LEN(@SQLSTR) - LEN(@NL + N'UNION' + @NL)))
EXEC sp_executesql @SQLSTR
GO
August 14, 2015 at 5:40 am
All I want is to count actual column count per all tables (and schema too)
row count is sometime can be misleading (nulls, 0's or ' ' spaces)
Your sproc produce an error
Msg 537, Level 16, State 3, Procedure EE_PROFILE_TABLE, Line 179
Invalid length parameter passed to the LEFT or SUBSTRING function.
Thanks,
Ed Dror
August 14, 2015 at 5:44 am
Sorry
This error Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value '2171.29' to data type int.
but this has nothing to do what I asked
Thanks,
Ed Dror
August 14, 2015 at 5:46 am
Column row count
August 14, 2015 at 6:59 am
odeddror (8/14/2015)
All I want is to count actual column count per all tables (and schema too)row count is sometime can be misleading (nulls, 0's or ' ' spaces)
Your sproc produce an error
Msg 537, Level 16, State 3, Procedure EE_PROFILE_TABLE, Line 179
Invalid length parameter passed to the LEFT or SUBSTRING function.
Thanks,
Ed Dror
Remove the brackets from the schema and table names
😎
August 14, 2015 at 8:10 am
odeddror (8/14/2015)
All I want is to count actual column count per all tables (and schema too)row count is sometime can be misleading (nulls, 0's or ' ' spaces)
Your sproc produce an error
Msg 537, Level 16, State 3, Procedure EE_PROFILE_TABLE, Line 179
Invalid length parameter passed to the LEFT or SUBSTRING function.
Thanks,
Ed Dror
Tell you what, instead of telling us what the output should be, show us what it should look like with some examples.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply