SSMS Procedures

ColumnAnalysis.sql

-- SQL/table/db profilers are better.. this works for something quick and simple
DECLARE @counter INT;
DECLARE @colname VARCHAR(100);
DECLARE @tablename varchar(100) = '<TABLENAME HERE>';

CREATE TABLE #qryresults (result varchar(100));
EXEC ('INSERT INTO #qryresults (result) SELECT ''TotalRows for ' + @tablename + ': '' + CAST(COUNT(1) AS VARCHAR(100)) AS res FROM dbo.' + @tablename + '');

DECLARE cursorval CURSOR FAST_FORWARD
FOR --SELECT TOP 5 COLUMN_NAME
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename;

OPEN cursorval
FETCH NEXT FROM cursorval INTO @colname

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC ('INSERT INTO #qryresults (result) SELECT ''' + @colname + ' | DistinctCount: '' + CAST(COUNT(DISTINCT ' + @colname + ') AS VARCHAR(100)) + '' | NullRatio: '' + CAST(CAST(COUNT(1)-COUNT(NULLIF(RTRIM(' + @colname + '),'''')) AS NUMERIC (13,2)) / CAST(COUNT(1) AS NUMERIC (13,2)) AS VARCHAR(100)) AS res FROM [dbo].' + @tablename + '')
	FETCH NEXT FROM cursorval INTO @colname
END


SELECT * FROM #qryresults

CLOSE cursorval
DEALLOCATE cursorval


DROP TABLE #qryresults;

SchemaAnalysis.sql

-- DBA's will hate me.. Don't use this thing...
DECLARE @counter INT;
DECLARE @colname VARCHAR(100);
DECLARE @tablename VARCHAR(100);
DECLARE @schemaname varchar(100) = 'dbo';

create table #qryresults (result varchar(100));

DECLARE cursorval CURSOR FAST_FORWARD
FOR SELECT TOP 5
COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schemaname
UNION ALL
SELECT DISTINCT TOP 5
'TABLETOTAL' AS COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schemaname;

OPEN cursorval
FETCH NEXT FROM cursorval INTO @colname, @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @colname = 'TABLETOTAL'
		BEGIN
			EXEC ('INSERT INTO #qryresults (result) SELECT ''TotalRows for ' + @tablename + ': '' + CAST(COUNT(1) AS VARCHAR(100)) AS res FROM dbo.' + @tablename + '');
		END
	ELSE
		BEGIN
			EXEC ('INSERT INTO #qryresults (result) SELECT ''' + @tablename + '.' + @colname + ' | DistinctCount: '' + CAST(COUNT(DISTINCT ' + @colname + ') AS VARCHAR(100)) + '' | NullRatio: '' + CAST(CAST(COUNT(1)-COUNT(NULLIF(RTRIM(' + @colname + '),'''')) AS NUMERIC (13,2)) / CAST(COUNT(1) AS NUMERIC (13,2)) AS VARCHAR(100)) AS res FROM [dbo].' + @tablename + '')
		END
	FETCH NEXT FROM cursorval INTO @colname, @tablename
END


SELECT * FROM #qryresults

CLOSE cursorval
DEALLOCATE cursorval


drop table #qryresults;