Analyze a Table Contents

,

Analyze a table contents for type of information. Returns the number of distinct values for each field and the list and frequency of those values when specified

CREATE PROCEDURE dbo.p_Analyze_Table_Content(@Table_Name nvarchar(255),@Maximum_Distinct_Value int)
AS

/*
Analyzes the content of a table
Returns the number of distinct values for each field in the table
Returns the occurance of each distinct value when total number of distinct values
is below threshold value
*/

--get a count of distinct values for each column in the table specified
SELECT 'SELECT COUNT(DISTINCT '+ C.name + ') AS [DISTINCT_' + C.name + '] FROM ' + @Table_Name 
FROM syscolumns C 
INNER JOIN sysobjects O
ON C.id = O.id
WHERE O.name = @Table_Name

--if number of distinct is less than max, get a distribution of those values

SELECT 'IF (SELECT COUNT(DISTINCT '+ C.name + ') AS [DISTINCT_' + C.name + '] FROM ' + @Table_Name + ') < ' + CONVERT(nvarchar(255),@Maximum_Distinct_Value) + ' 	BEGIN
		SELECT ' + C.name + ',Count(*) AS [Count_' + C.name + ']
		FROM ' + @Table_Name + '
		GROUP BY ' + C.name + '
		ORDER BY ' + C.name + '
	END
	'
FROM syscolumns C 
INNER JOIN sysobjects O
ON C.id = O.id
WHERE O.name = @Table_Name

Rate

Share

Share

Rate