Результаты не возвращены (не правильно) из запроса на количество пустых значений, MinValue,MaxValue и AvgValue
У меня есть запрос ниже, который должен вернуть записи для числа записей NULL, MinValue столбца, MaxValue и AvgValue. Тем не менее, запрос выполняется, но не возвращает записи, а только заголовки столбцов, когда это определенно необходимо. Можете ли вы исправить мой код, чтобы показать, где я иду не так? Рассчитанные поля должны применяться только к числовым, плавающим, десятичным и другим выбранным типам данных, как определено в запросе ниже.
DECLARE @schemaName AS sysname;
DECLARE @tableName AS sysname;
DECLARE @columnName AS sysname;
DECLARE @schema_id AS int;
DECLARE @object_id AS int;
DECLARE @column_id AS int;
DECLARE @isNullable AS bit;
DECLARE @lastSchema_id AS int;
DECLARE @lastTable_id AS int;
DECLARE @recordCount AS bigint;
DECLARE @MinValue As int;
DECLARE @MaxValue As int;
DECLARE @AvgValue As int;
DECLARE @nullCnt AS bigint;
DECLARE @SQL as nvarchar(max);
DECLARE @paramDefinition NVARCHAR(max);
if exists(select name from tempdb..sysobjects where name LIKE'#Columns%')
DROP TABLE #Columns;
CREATE TABLE #Columns (
schema_id int,
object_id int,
column_id int,
schemaName sysname,
tableName sysname,
columnName sysname,
recordCnt bigint,
MinValue int,
MaxValue int,
AvgValue int,
nullCnt bigint,
nullPct numeric(38,35) );
-- Set to the @lastSchema_id and @lastTable_id to NULL so that the first
-- loop through the cursor the record count is generated.
SET @lastSchema_id = NULL;
SET @lastTable_id = NULL;
-- List of all the user schemas.tables.columns
-- in the database
DECLARE c_Cursor CURSOR FOR
SELECT schemas.schema_id
, all_objects.object_id
, all_columns.column_id
, schemas.name AS schemaName
, all_objects.name AS tableName
, all_columns.name AS columnName
, all_columns.is_nullable
FROM sys.schemas
INNER JOIN sys.all_objects
ON schemas.schema_id = all_objects.schema_id
AND all_objects.type = 'U'
INNER JOIN sys.all_columns
ON all_objects.object_id = all_columns.object_id
WHERE all_objects.type LIKE '%int%'
OR all_objects.type LIKE '%float%'
OR all_objects.type LIKE '%decimal%'
OR all_objects.type LIKE '%numeric%'
OR all_objects.type LIKE '%real%'
OR all_objects.type LIKE '%money%'
ORDER BY schemas.schema_id
, all_objects.object_id
, all_columns.column_id;
OPEN c_Cursor;
FETCH NEXT FROM c_Cursor
INTO @schema_id
, @object_id
, @column_id
, @schemaName
, @tableName
, @columnName
, @isNullable;
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get the record count for the table we are currently working on if this is
-- the first time we are encountering the table.
IF ( ( @schema_id <> @lastSchema_id ) OR ( @object_id <> @lastTable_id )
OR ( @lastSchema_id IS NULL ) OR ( @lastTable_id IS NULL ) )
BEGIN
SET @SQL = N'SELECT @recordCount = COUNT(1) FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + ';';
SET @paramDefinition = N'@recordCount bigint OUTPUT';
exec sp_executesql @SQL,
@paramDefinition,
@recordCount = @recordCount OUTPUT;
END
-- Get the min value for the table
IF ( ( @schema_id <> @lastSchema_id ) OR ( @object_id <> @lastTable_id )
OR ( @lastSchema_id IS NULL ) OR ( @lastTable_id IS NULL ) )
BEGIN
SET @SQL = N'SELECT @MinValue = COUNT(1) FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + ';';
SET @paramDefinition = N'@MinValue int OUTPUT';
exec sp_executesql @SQL,
@paramDefinition,
@MinValue = @MinValue OUTPUT;
END
--Get the max value for the table
IF ( ( @schema_id <> @lastSchema_id ) OR ( @object_id <> @lastTable_id )
OR ( @lastSchema_id IS NULL ) OR ( @lastTable_id IS NULL ) )
BEGIN
SET @SQL = N'SELECT @MaxValue = COUNT(1) FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + ';';
SET @paramDefinition = N'@MaxValue int OUTPUT';
exec sp_executesql @SQL,
@paramDefinition,
@MaxValue = @MaxValue OUTPUT;
END
--Get the avg value for the table
IF ( ( @schema_id <> @lastSchema_id ) OR ( @object_id <> @lastTable_id )
OR ( @lastSchema_id IS NULL ) OR ( @lastTable_id IS NULL ) )
BEGIN
SET @SQL = N'SELECT @AvgValue = COUNT(1) FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + ';';
SET @paramDefinition = N'@AvgValue int OUTPUT';
exec sp_executesql @SQL,
@paramDefinition,
@AvgValue = @AvgValue OUTPUT;
END
-- If the column is NOT NULL, there is no reason to do a count
-- Set the nullCnt and nullPct to 0
IF ( @isNullable = 0 )
BEGIN
INSERT INTO #Columns
( [schema_id]
, [object_id]
, column_id
, schemaName
, tableName
, columnName
, recordCnt
, nullCnt
, nullPct )
VALUES
( @schema_id
, @object_id
, @column_id
, @schemaName
, @tableName
, @columnName
, @recordCount
, 0
, 0.0 );
END
-- If the column is NULL, count the number of NULL fields in the table.
ELSE
BEGIN
SET @SQL = N'SELECT @nullCnt = COUNT(1) FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) +
N' WHERE ' + QUOTENAME(@columnName) + N' IS NULL;';
SET @paramDefinition = N'@nullCnt bigint OUTPUT';
PRINT @SQL;
exec sp_executesql @SQL,
@paramDefinition,
@nullCnt = @nullCnt OUTPUT;
INSERT INTO #Columns
( [schema_id]
, [object_id]
, column_id
, schemaName
, tableName
, columnName
, recordCnt
, nullCnt
, nullPct )
VALUES
( @schema_id
, @object_id
, @column_id
, @schemaName
, @tableName
, @columnName
, @recordCount
, @nullCnt
-- USE NULLIF in case there are no recods in the table
, ISNULL( @nullCnt * 1.0 / NULLIF( @recordCount, 0) * 100.0, 0 ) );
END
-- Set the @lastSchema_id and @lastTable_id so that on
-- the next loop, if it's the same table there is no
-- need to recount the columns for the table.
SET @lastSchema_id = @schema_id;
SET @lastTable_id = @object_id;
FETCH NEXT FROM c_Cursor
INTO @schema_id
, @object_id
, @column_id
, @schemaName
, @tableName
, @columnName
, @isNullable;
END;
CLOSE c_Cursor;
DEALLOCATE c_Cursor;
SELECT *
FROM #Columns;
1 ответ
Проблема заключается в первом запросе, который вы используете для курсора:
SELECT schemas.schema_id
, all_objects.object_id
, all_columns.column_id
, schemas.name AS schemaName
, all_objects.name AS tableName
, all_columns.name AS columnName
, all_columns.is_nullable
FROM sys.schemas
INNER JOIN sys.all_objects
ON schemas.schema_id = all_objects.schema_id
AND all_objects.type = 'U'
INNER JOIN sys.all_columns
ON all_objects.object_id = all_columns.object_id
WHERE all_objects.type LIKE '%int%'
OR all_objects.type LIKE '%float%'
OR all_objects.type LIKE '%decimal%'
OR all_objects.type LIKE '%numeric%'
OR all_objects.type LIKE '%real%'
OR all_objects.type LIKE '%money%'
ORDER BY schemas.schema_id
, all_objects.object_id
, all_columns.column_id;
Если вы внимательно проверьте, вы делаете INNER JOIN на all_object.type = 'U' так что все типы должны быть U но потом в WHERE Вы делаете дополнительный фильтр в том же oclumn с типами столбцов, которые всегда будут ложными, так как вы уже фильтруете по типу U,
Я думаю, что вы хотели фильтровать по типу данных столбца, в котором вам нужно будет выполнить дополнительное объединение и правильно фильтровать, как показано ниже:
SELECT
schemas.schema_id
, all_objects.object_id
, all_columns.column_id
, schemas.name AS schemaName
, all_objects.name AS tableName
, all_columns.name AS columnName
, all_columns.is_nullable
FROM
sys.schemas
INNER JOIN sys.all_objects
ON schemas.schema_id = all_objects.schema_id
AND all_objects.type = 'U'
INNER JOIN sys.all_columns
ON all_objects.object_id = all_columns.object_id
INNER JOIN sys.types
ON all_columns.system_type_id = types.system_type_id
AND all_columns.user_type_id = types.user_type_id
WHERE
types.name LIKE '%int%'
OR types.name LIKE '%float%'
OR types.name LIKE '%decimal%'
OR types.name LIKE '%numeric%'
OR types.name LIKE '%real%'
OR types.name LIKE '%money%'
ORDER BY
schemas.schema_id
, all_objects.object_id
, all_columns.column_id;
РЕДАКТИРОВАТЬ: Вот исправленный полный скрипт. Ошибки, упомянутые ниже.
DECLARE @schemaName AS sysname;
DECLARE @tableName AS sysname;
DECLARE @columnName AS sysname;
DECLARE @schema_id AS int;
DECLARE @object_id AS int;
DECLARE @column_id AS int;
DECLARE @isNullable AS bit;
DECLARE @lastSchema_id AS int;
DECLARE @lastTable_id AS int;
DECLARE @recordCount AS bigint;
DECLARE @MinValue As int;
DECLARE @MaxValue As int;
DECLARE @AvgValue As int;
DECLARE @nullCnt AS bigint;
DECLARE @SQL as nvarchar(max);
DECLARE @paramDefinition NVARCHAR(max);
if exists(select name from tempdb..sysobjects where name LIKE'#Columns%')
DROP TABLE #Columns;
CREATE TABLE #Columns (
schema_id int,
object_id int,
column_id int,
schemaName sysname,
tableName sysname,
columnName sysname,
recordCnt bigint,
MinValue int,
MaxValue int,
AvgValue int,
nullCnt bigint,
nullPct numeric(38,35) );
-- Set to the @lastSchema_id and @lastTable_id to NULL so that the first
-- loop through the cursor the record count is generated.
SET @lastSchema_id = NULL;
SET @lastTable_id = NULL;
-- List of all the user schemas.tables.columns
-- in the database
DECLARE c_Cursor CURSOR FOR
SELECT
schemas.schema_id
, all_objects.object_id
, all_columns.column_id
, schemas.name AS schemaName
, all_objects.name AS tableName
, all_columns.name AS columnName
, all_columns.is_nullable
FROM
sys.schemas
INNER JOIN sys.all_objects
ON schemas.schema_id = all_objects.schema_id
AND all_objects.type = 'U'
INNER JOIN sys.all_columns
ON all_objects.object_id = all_columns.object_id
INNER JOIN sys.types
ON all_columns.system_type_id = types.system_type_id
AND all_columns.user_type_id = types.user_type_id
WHERE
types.name LIKE '%int%'
OR types.name LIKE '%float%'
OR types.name LIKE '%decimal%'
OR types.name LIKE '%numeric%'
OR types.name LIKE '%real%'
OR types.name LIKE '%money%'
ORDER BY
schemas.schema_id
, all_objects.object_id
, all_columns.column_id;
OPEN c_Cursor;
FETCH NEXT FROM c_Cursor
INTO @schema_id
, @object_id
, @column_id
, @schemaName
, @tableName
, @columnName
, @isNullable;
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get the record count for the table we are currently working on if this is
-- the first time we are encountering the table.
IF ( ( @schema_id <> @lastSchema_id ) OR ( @object_id <> @lastTable_id )
OR ( @lastSchema_id IS NULL ) OR ( @lastTable_id IS NULL ) )
BEGIN
SET @SQL = N'SELECT @recordCount = COUNT(1) FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + ';';
SET @paramDefinition = N'@recordCount bigint OUTPUT';
exec sp_executesql @SQL,
@paramDefinition,
@recordCount = @recordCount OUTPUT;
END
-- Get the min value for the table
IF ( ( @schema_id <> @lastSchema_id ) OR ( @object_id <> @lastTable_id )
OR ( @lastSchema_id IS NULL ) OR ( @lastTable_id IS NULL ) )
BEGIN
SET @SQL = N'SELECT @MinValue = MIN(' + QUOTENAME(@columnName) + N') FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + ';';
SET @paramDefinition = N'@MinValue int OUTPUT';
exec sp_executesql @SQL,
@paramDefinition,
@MinValue = @MinValue OUTPUT;
END
--Get the max value for the table
IF ( ( @schema_id <> @lastSchema_id ) OR ( @object_id <> @lastTable_id )
OR ( @lastSchema_id IS NULL ) OR ( @lastTable_id IS NULL ) )
BEGIN
SET @SQL = N'SELECT @MaxValue = MAX(' + QUOTENAME(@columnName) + N') FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + ';';
SET @paramDefinition = N'@MaxValue int OUTPUT';
exec sp_executesql @SQL,
@paramDefinition,
@MaxValue = @MaxValue OUTPUT;
END
--Get the avg value for the table
IF ( ( @schema_id <> @lastSchema_id ) OR ( @object_id <> @lastTable_id )
OR ( @lastSchema_id IS NULL ) OR ( @lastTable_id IS NULL ) )
BEGIN
SET @SQL = N'SELECT @AvgValue = AVG(' + QUOTENAME(@columnName) + N') FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + ';';
SET @paramDefinition = N'@AvgValue int OUTPUT';
exec sp_executesql @SQL,
@paramDefinition,
@AvgValue = @AvgValue OUTPUT;
END
-- If the column is NOT NULL, there is no reason to do a count
-- Set the nullCnt and nullPct to 0
IF ( @isNullable = 0 )
BEGIN
INSERT INTO #Columns
( [schema_id]
, [object_id]
, column_id
, schemaName
, tableName
, columnName
, recordCnt
, nullCnt
, nullPct
,MinValue
,MaxValue
,AvgValue )
SELECT
@schema_id
, @object_id
, @column_id
, @schemaName
, @tableName
, @columnName
, @recordCount
, 0
, 0
, @MinValue
, @MaxValue
, @AvgValue
END
-- If the column is NULL, count the number of NULL fields in the table.
ELSE
BEGIN
SET @SQL = N'SELECT @nullCnt = COUNT(1) FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) +
N' WHERE ' + QUOTENAME(@columnName) + N' IS NULL;';
SET @paramDefinition = N'@nullCnt bigint OUTPUT';
PRINT @SQL;
exec sp_executesql @SQL,
@paramDefinition,
@nullCnt = @nullCnt OUTPUT;
INSERT INTO #Columns
( [schema_id]
, [object_id]
, column_id
, schemaName
, tableName
, columnName
, recordCnt
, nullCnt
, nullPct
,MinValue
,MaxValue
,AvgValue )
SELECT
@schema_id
, @object_id
, @column_id
, @schemaName
, @tableName
, @columnName
, @recordCount
, @nullCnt
-- USE NULLIF in case there are no recods in the table
, ISNULL( @nullCnt * 1.0 / NULLIF( @recordCount, 0) * 100.0, 0 )
, @MinValue
, @MaxValue
, @AvgValue
END
-- Set the @lastSchema_id and @lastTable_id so that on
-- the next loop, if it's the same table there is no
-- need to recount the columns for the table.
SET @lastSchema_id = @schema_id;
SET @lastTable_id = @object_id;
FETCH NEXT FROM c_Cursor
INTO @schema_id
, @object_id
, @column_id
, @schemaName
, @tableName
, @columnName
, @isNullable;
END;
CLOSE c_Cursor;
DEALLOCATE c_Cursor;
SELECT *
FROM #Columns;
Ошибки:
- Вы рассчитывали среднее, минимальное и максимальное значения как
COUNTвместо правильной операции. - Средние, минимальные и максимальные значения не были применены к правильному столбцу, это всегда был жестко закодированный 1.
- Вы вычисляли средние, минимальные и максимальные значения, но не вставляли их во временную таблицу.