在SQL Server中创建一个存储过程,该存储过程返回表中的列总数和每列中的值的不同数目 [英] Create a stored procedure in SQL Server that returns total count of columns in table and distinct count of values in each column
本文介绍了在SQL Server中创建一个存储过程,该存储过程返回表中的列总数和每列中的值的不同数目的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要一个存储过程,该存储过程将任何表名作为参数,并在执行时返回该特定表的行总数和值的不同计数.
I need a stored procedure that takes any table name as a parameter and returns total count of rows and distinct count of values in each column of that particular table when executed.
让我们看下面的示例表:
Let's take the sample table as below:
create table journey
(
Src varchar(255),
Dest varchar(255)
)
insert into journey
values ('Jaipur', 'Mumbai'), ('Mumbai', 'Jaipur'), ('Kolkata', 'Bangalore'),
('Bangalore', 'Indore'),('Indore', 'Lucknow'), ('Lucknow', 'Indore')
有人可以帮助我完成动态SQL任务吗?
Can anybody help me with this task of dynamic SQL.
我做了一个尝试,没有给出结果.有人可以建议代码落后吗
I have made an attempt which is not giving the result. Can anybody suggest where the code is lagging
create procedure test @table_name varchar(255)
AS
BEGIN
SELECT count(*) as [Number of columns]
FROM information_schema.columns
WHERE table_name = '@table_name'
SELECT @table_name =
Replace(
'SELECT ' + SUBSTRING((SELECT ',' +'COUNT(DISTINCT(' + column_name + ')) As '
+ column_name + ' ' + CHAR(13) + CHAR(10)
FROM information_schema.columns
WHERE table_name = '@table_name'
for xml path('')),2,200000) + 'FROM @table_name','
','')
exec(@table_name)
END
exec test @table_name = "journey";
推荐答案
This is an extension of something I wrote on SQL Server Central awhile ago, that includes a COUNT
of both non-NULL
and (optionally)DISTINCT
values:
DECLARE @Schema sysname, @Table sysname, @IncDistinct bit = 1;
SET @Schema = 'dbo';
SET @Table = 'icp_yyclient';
DECLARE @SQL nvarchar(MAX)
SET @SQL = N'WITH Counts AS (' + NCHAR(13) + NCHAR(10) +
N' SELECT @Schema AS SchemaName,' + NCHAR(13) + NCHAR(10) +
N' @Table AS TableName,' +
STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
N' COUNT(' + CASE WHEN C.DATA_TYPE IN ('text','image') THEN '1' ELSE QUOTENAME(C.COLUMN_NAME) END + N') AS ' + QUOTENAME(COLUMN_NAME) + N',' + NCHAR(13) + NCHAR(10) +
N' COUNT(DISTINCT ' + CASE WHEN C.DATA_TYPE IN ('text','image') THEN '1' ELSE QUOTENAME(C.COLUMN_NAME) END + N') AS ' + QUOTENAME(COLUMN_NAME + N'_Distinct')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_SCHEMA = @Schema
AND C.TABLE_NAME = @Table
--AND C.DATA_TYPE NOT IN ('text','image')
ORDER BY C.ORDINAL_POSITION
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,14,N'') + NCHAR(13) + NCHAR(10) +
N' FROM ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + N')' + NCHAR(13) + NCHAR(10) +
N'SELECT V.OrdinalPosition,' + NCHAR(13) + NCHAR(10) +
N' V.ColumnName,' + NCHAR(13) + NCHAR(10) +
N' V.NonNullCount,' + NCHAR(13) + NCHAR(10) +
CASE WHEN @IncDistinct = 1 THEN N' V.DistinctCount,' + NCHAR(13) + NCHAR(10) ELSE N'' END +
N' ISC.DATA_TYPE + ISNULL(NULLIF(DT.S,''(*)''),'''') AS Datatype,' + NCHAR(13) + NCHAR(10) +
N' K.KeyType' + NCHAR(13) + NCHAR(10) +
N'FROM Counts C' + NCHAR(13) + NCHAR(10) +
N' CROSS APPLY(VALUES' + STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
N' (' + CONVERT(varchar(4),C.ORDINAL_POSITION) +N',N' + QUOTENAME(C.COLUMN_NAME,'''') + N',C.' + QUOTENAME(C.COLUMN_NAME) + N',C.' + QUOTENAME(C.COLUMN_NAME + N'_Distinct') + N')'
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = @Table
--AND C.DATA_TYPE NOT IN ('text','image')
ORDER BY C.ORDINAL_POSITION
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,26,N'') + N')V(OrdinalPosition,ColumnName,NonNullCount,DistinctCount)' + NCHAR(13) + NCHAR(10) +
N' JOIN INFORMATION_SCHEMA.COLUMNS ISC ON C.SchemaName = ISC.TABLE_SCHEMA' + NCHAR(13) + NCHAR(10) +
N' AND C.TableName = ISC.TABLE_NAME' + NCHAR(13) + NCHAR(10) +
N' AND V.ColumnName = ISC.COLUMN_NAME' + NCHAR(13) + NCHAR(10) +
N' CROSS APPLY (VALUES(''('' + STUFF(CONCAT('','' + CASE ISC.CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN ''MAX'' ELSE CONVERT(varchar(4),ISC.CHARACTER_MAXIMUM_LENGTH) END,' + NCHAR(13) + NCHAR(10)+
N' '','' + CASE WHEN ISC.DATA_TYPE NOT LIKE ''%int'' THEN CONVERT(varchar(4),ISC.NUMERIC_PRECISION) END,' + NCHAR(13) + NCHAR(10) +
N' '','' + CASE WHEN ISC.DATA_TYPE NOT LIKE ''%int'' THEN CONVERT(varchar(4),ISC.NUMERIC_SCALE) END,' + NCHAR(13) + NCHAR(10) +
N' '','' + CASE WHEN ISC.DATA_TYPE NOT IN (''datetime'',''smalldatetime'') THEN CONVERT(varchar(4),ISC.DATETIME_PRECISION) END),1,1,'''') + '')'')) DT(S)' + NCHAR(13) + NCHAR(10) +
N' OUTER APPLY(SELECT TC.CONSTRAINT_TYPE AS KeyType ' + NCHAR(13) + NCHAR(10) +
N' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC' + NCHAR(13) + NCHAR(10) +
N' JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA' + NCHAR(13) + NCHAR(10) +
N' AND TC.TABLE_NAME = KCU.TABLE_NAME' + NCHAR(13) + NCHAR(10) +
N' AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME' + NCHAR(13) + NCHAR(10) +
N' WHERE KCU.COLUMN_NAME = V.ColumnName' + NCHAR(13) + NCHAR(10) +
N' AND TC.TABLE_SCHEMA = ISC.TABLE_SCHEMA' + NCHAR(13) + NCHAR(10) +
N' AND TC.TABLE_NAME = ISC.TABLE_NAME) K' + NCHAR(13) + NCHAR(10) +
N'ORDER BY V.OrdinalPosition';
PRINT @SQL; --you will need to use the SELECT here if @SQL is over 4,000 characters
--SELECT @SQL;
EXEC sp_executesql @SQL, N'@Schema sysname,@Table sysname',@Schema = @Schema, @Table = @Table;
这篇关于在SQL Server中创建一个存储过程,该存储过程返回表中的列总数和每列中的值的不同数目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文