sqlserver/10-SQL-Server-Obter-Estatisticas-Coluna.sql at master · AH842/sqlserver · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnitPrice)
OVER (PARTITION BY NULL) AS MedianCont
FROM Sales.SalesOrderDetail

/*----Numeric Columns-----*/

select v.ColumnName as 'Nome da Coluna',
    COUNT(*) - COUNT(v.ColumnValue) AS 'Linhas Vazias',
	AVG(v.ColumnValue) as Média,
	STDEV(v.ColumnValue) as [Desv. Pad],
	MIN(v.ColumnValue) as Min,
	MAX(v.ColumnValue) as Máx
from Sales.SalesOrderDetail ea cross apply
     (values ('OrderQty', ea.OrderQty),
			 ('UnitPrice', ea.UnitPrice),
             ('UnitPriceDiscount', ea.UnitPriceDiscount),
             ('LineTotal', ea.LineTotal)
     ) v(ColumnName, ColumnValue)
GROUP BY v.ColumnName


DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + 'Sales' + '].[' + 'SalesOrderDetail' + '] WHERE '

   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + '' + ''' OR '
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = 'Sales'
   AND TABLE_NAME = 'SalesOrderDetail'
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

PRINT(@sqlCommand)


/*----String Columns-----*/

select v.ColumnName as 'Nome da Coluna',
    COUNT(*) - COUNT(v.ColumnValue) AS 'Linhas Vazias',
	COUNT(DISTINCT v.ColumnValue) AS 'Linhas Únicas',
	MIN(v.ColumnValue) AS 'Min',
	MAX(v.ColumnValue) AS 'Máx'
from Sales.SalesOrderDetail ea outer apply
     (values
		('SalesOrderID', CONVERT(varchar(max), SalesOrderID)),
		('ProductID', CONVERT(varchar(max), ProductID)),
		('CarrierTrackingNumber', CarrierTrackingNumber),
		('SpecialOfferID', CONVERT(varchar(max), SpecialOfferID))
     ) v(ColumnName, ColumnValue)
GROUP BY v.ColumnName


DESCRIBE 'Sales.SalesOrderDetail';


select v.ColumnName as 'Nome da Coluna',
    COUNT(*) - COUNT(v.ColumnValue) AS 'Linhas Vazias'
	--COUNT(DISTINCT v.ColumnValue) AS 'Linhas Únicas',
	--MIN(v.ColumnValue) AS 'Min',
	--MAX(v.ColumnValue) AS 'Máx'
from Sales.SalesOrderDetail ea
outer apply
(
	SELECT tc.COLUMN_NAME AS ColumnName, ea.CarrierTrackingNumber AS ColumnValue
	FROM INFORMATION_SCHEMA.COLUMNS tc           -- nome da tabela de colunas do sistema
	WHERE tc.DATA_TYPE IN ('nvarchar') -- Filtrar coluna pelo tipo de datos
		  AND tc.TABLE_NAME = 'SalesOrderDetail'
) AS v
GROUP BY v.ColumnName


SELECT tc.COLUMN_NAME AS ColumnName,
    [COUNT] = (SELECT COUNT(*) FROM Sales.SalesOrderDetail)
FROM INFORMATION_SCHEMA.COLUMNS tc           -- nome da tabela de colunas do sistema
WHERE tc.DATA_TYPE IN ('nvarchar') -- Filtrar coluna pelo tipo de datos
		  AND tc.TABLE_NAME = 'SalesOrderDetail'


/***-------*/

	SELECT
	'Mean' as 'Coluna',	AVG(UnitPrice) as 'UnitPrice',
	AVG(UnitPriceDiscount)as 'UnitPriceDiscount',AVG(LineTotal) as 'LineTotal'
	FROM Sales.SalesOrderDetail

/*------------------------------*/

	SELECT
	'Mean' as 'Coluna',	AVG(UnitPrice) as 'UnitPrice',
	AVG(UnitPriceDiscount)as 'UnitPriceDiscount',
	AVG(LineTotal) as 'LineTotal'
	FROM Sales.SalesOrderDetail

	UNION

	SELECT
	'Min' as 'Coluna',	MIN(UnitPrice) as 'UnitPrice',
	MIN(UnitPriceDiscount)as 'UnitPriceDiscount',
	MIN(LineTotal) as 'LineTotal'
	FROM Sales.SalesOrderDetail


	UNION

	SELECT
	'Max' as 'Coluna',	MAX(UnitPrice) as 'UnitPrice',
	MAX(UnitPriceDiscount)as 'UnitPriceDiscount',
	MAX(LineTotal) as 'LineTotal'
	FROM Sales.SalesOrderDetail

	UNION

	SELECT
	'Std' as 'Coluna',	STDEV(UnitPrice) as 'UnitPrice',
	STDEV(UnitPriceDiscount)as 'UnitPriceDiscount',
	STDEV(LineTotal) as 'LineTotal'
	FROM Sales.SalesOrderDetail


SELECT
    name,
    system_type_id,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    'select * from Sales.SalesOrderDetail',
    null,
    0);


	sys.