DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @tot INT;
SELECT @tot = COUNT(*) FROM app.patient_fact_detail
select @tot
SELECT @sql += ' UNION ' + CHAR(13) + CHAR(10)
+ 'SELECT '
+ '''' + COLUMN_NAME + ''' as Col, ' + cast(ORDINAL_POSITION as varchar) +
' as OrdinalPosition, '
+ ' count(' + COLUMN_NAME + ') as NumWithVal, '
+ '(count(' + COLUMN_NAME + ') / CAST(' + CAST(@tot as varchar(20)) + ' as
decimal) * 100) as Percnt '
+ 'FROM ' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'patient_fact_detail';
SELECT @sql = SUBSTRING(@sql, CHARINDEX(char(10), @sql), LEN(@sql)) -- remove
first line (extra union)
SELECT @sql += CHAR(13) + CHAR(10) + 'ORDER BY 3 DESC, OrdinalPosition'
select @sql
SELECT 'id' as Col, 1 as OrdinalPosition, count(id) as NumWithVal, (count(id) /
CAST(355963 as decimal) * 100) as Percnt
FROM app.patient_fact_detail UNION SELECT 'empi' as Col, 2 as OrdinalPosition,
count(empi) as NumWithVal, (count(empi) / CAST(355963 as decimal) * 100) as Percnt
FROM [patient_fact_detail] UNION SELECT 'patient_id' as Col, 3 as
OrdinalPosition, count(patient_id) as NumWithVal, (count(patient_id) / CAST(355963
as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT 'active' as
Col, 4 as OrdinalPosition, count(active) as NumWithVal, (count(active) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'communication_id' as Col, 5 as OrdinalPosition, count(communication_id) as
NumWithVal, (count(communication_id) / CAST(355963 as decimal) * 100) as Percnt
FROM [patient_fact_detail] UNION SELECT 'communication' as Col, 6 as
OrdinalPosition, count(communication) as NumWithVal, (count(communication) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'ethnicity_id' as Col, 7 as OrdinalPosition, count(ethnicity_id) as NumWithVal,
(count(ethnicity_id) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'ethnicity' as Col, 8 as OrdinalPosition,
count(ethnicity) as NumWithVal, (count(ethnicity) / CAST(355963 as decimal) * 100)
as Percnt FROM [patient_fact_detail] UNION SELECT 'gender_id' as Col, 9 as
OrdinalPosition, count(gender_id) as NumWithVal, (count(gender_id) / CAST(355963
as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT 'gender' as
Col, 10 as OrdinalPosition, count(gender) as NumWithVal, (count(gender) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'initial_status_id' as Col, 11 as OrdinalPosition, count(initial_status_id) as
NumWithVal, (count(initial_status_id) / CAST(355963 as decimal) * 100) as Percnt
FROM [patient_fact_detail] UNION SELECT 'initial_status' as Col, 12 as
OrdinalPosition, count(initial_status) as NumWithVal, (count(initial_status) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'current_status_id' as Col, 13 as OrdinalPosition, count(current_status_id) as
NumWithVal, (count(current_status_id) / CAST(355963 as decimal) * 100) as Percnt
FROM [patient_fact_detail] UNION SELECT 'current_status' as Col, 14 as
OrdinalPosition, count(current_status) as NumWithVal, (count(current_status) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'current_status_detail_id' as Col, 15 as OrdinalPosition,
count(current_status_detail_id) as NumWithVal, (count(current_status_detail_id) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'current_status_detail' as Col, 16 as OrdinalPosition,
count(current_status_detail) as NumWithVal, (count(current_status_detail) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'axle_health_patient_id' as Col, 17 as OrdinalPosition,
count(axle_health_patient_id) as NumWithVal, (count(axle_health_patient_id) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'locked_for_schedule' as Col, 18 as OrdinalPosition, count(locked_for_schedule) as
NumWithVal, (count(locked_for_schedule) / CAST(355963 as decimal) * 100) as Percnt
FROM [patient_fact_detail] UNION SELECT 'patient_risk' as Col, 19 as
OrdinalPosition, count(patient_risk) as NumWithVal, (count(patient_risk) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'memb_keyid' as Col, 20 as OrdinalPosition, count(memb_keyid) as NumWithVal,
(count(memb_keyid) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'AHA_Dt' as Col, 21 as OrdinalPosition,
count(AHA_Dt) as NumWithVal, (count(AHA_Dt) / CAST(355963 as decimal) * 100) as
Percnt FROM [patient_fact_detail] UNION SELECT 'last_attempt_date' as Col, 22 as
OrdinalPosition, count(last_attempt_date) as NumWithVal, (count(last_attempt_date)
/ CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION
SELECT 'status_outcome_id' as Col, 23 as OrdinalPosition, count(status_outcome_id)
as NumWithVal, (count(status_outcome_id) / CAST(355963 as decimal) * 100) as Percnt
FROM [patient_fact_detail] UNION SELECT 'last_viewed' as Col, 24 as
OrdinalPosition, count(last_viewed) as NumWithVal, (count(last_viewed) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'fname' as Col, 25 as OrdinalPosition, count(fname) as NumWithVal, (count(fname) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'lname' as Col, 26 as OrdinalPosition, count(lname) as NumWithVal, (count(lname) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'birthdate' as Col, 27 as OrdinalPosition, count(birthdate) as NumWithVal,
(count(birthdate) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'patient_to_address' as Col, 28 as
OrdinalPosition, count(patient_to_address) as NumWithVal,
(count(patient_to_address) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'patient_to_client' as Col, 29 as
OrdinalPosition, count(patient_to_client) as NumWithVal, (count(patient_to_client)
/ CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION
SELECT 'patient_to_healthplan' as Col, 30 as OrdinalPosition,
count(patient_to_healthplan) as NumWithVal, (count(patient_to_healthplan) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'patient_to_project' as Col, 31 as OrdinalPosition, count(patient_to_project) as
NumWithVal, (count(patient_to_project) / CAST(355963 as decimal) * 100) as Percnt
FROM [patient_fact_detail] UNION SELECT 'patient_to_telecom' as Col, 32 as
OrdinalPosition, count(patient_to_telecom) as NumWithVal,
(count(patient_to_telecom) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'encounters' as Col, 33 as OrdinalPosition,
count(encounters) as NumWithVal, (count(encounters) / CAST(355963 as decimal) *
100) as Percnt FROM [patient_fact_detail] UNION SELECT 'users' as Col, 34 as
OrdinalPosition, count(users) as NumWithVal, (count(users) / CAST(355963 as
decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT 'awvtatus2' as
Col, 35 as OrdinalPosition, count(awvtatus2) as NumWithVal, (count(awvtatus2) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'awvtatus3' as Col, 36 as OrdinalPosition, count(awvtatus3) as NumWithVal,
(count(awvtatus3) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'client_ids' as Col, 37 as OrdinalPosition,
count(client_ids) as NumWithVal, (count(client_ids) / CAST(355963 as decimal) *
100) as Percnt FROM [patient_fact_detail] UNION SELECT 'client' as Col, 38 as
OrdinalPosition, count(client) as NumWithVal, (count(client) / CAST(355963 as
decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT 'project_ids'
as Col, 39 as OrdinalPosition, count(project_ids) as NumWithVal,
(count(project_ids) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'project' as Col, 40 as OrdinalPosition,
count(project) as NumWithVal, (count(project) / CAST(355963 as decimal) * 100) as
Percnt FROM [patient_fact_detail] UNION SELECT 'encounter_users' as Col, 41 as
OrdinalPosition, count(encounter_users) as NumWithVal, (count(encounter_users) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'appointment_users' as Col, 42 as OrdinalPosition, count(appointment_users) as
NumWithVal, (count(appointment_users) / CAST(355963 as decimal) * 100) as Percnt
FROM [patient_fact_detail] UNION SELECT 'client_id' as Col, 43 as
OrdinalPosition, count(client_id) as NumWithVal, (count(client_id) / CAST(355963
as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'updated_datetime' as Col, 44 as OrdinalPosition, count(updated_datetime) as
NumWithVal, (count(updated_datetime) / CAST(355963 as decimal) * 100) as Percnt
FROM [patient_fact_detail] UNION SELECT 'eligibility' as Col, 45 as
OrdinalPosition, count(eligibility) as NumWithVal, (count(eligibility) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'axle_health_address_id' as Col, 46 as OrdinalPosition,
count(axle_health_address_id) as NumWithVal, (count(axle_health_address_id) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'address1' as Col, 47 as OrdinalPosition, count(address1) as NumWithVal,
(count(address1) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'address2' as Col, 48 as OrdinalPosition,
count(address2) as NumWithVal, (count(address2) / CAST(355963 as decimal) * 100) as
Percnt FROM [patient_fact_detail] UNION SELECT 'postal' as Col, 49 as
OrdinalPosition, count(postal) as NumWithVal, (count(postal) / CAST(355963 as
decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT 'city_name' as
Col, 50 as OrdinalPosition, count(city_name) as NumWithVal, (count(city_name) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'state_name' as Col, 51 as OrdinalPosition, count(state_name) as NumWithVal,
(count(state_name) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'county_name' as Col, 52 as OrdinalPosition,
count(county_name) as NumWithVal, (count(county_name) / CAST(355963 as decimal) *
100) as Percnt FROM [patient_fact_detail] UNION SELECT 'pcp_NPI' as Col, 53 as
OrdinalPosition, count(pcp_NPI) as NumWithVal, (count(pcp_NPI) / CAST(355963 as
decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT 'pcp_fname' as
Col, 54 as OrdinalPosition, count(pcp_fname)
as NumWithVal, (count(pcp_fname) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'pcp_lname' as Col, 55 as OrdinalPosition,
count(pcp_lname) as NumWithVal, (count(pcp_lname) / CAST(355963 as decimal) * 100)
as Percnt FROM [patient_fact_detail] UNION SELECT 'member_id' as Col, 56 as
OrdinalPosition, count(member_id) as NumWithVal, (count(member_id) / CAST(355963
as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT 'pcp_phone'
as Col, 57 as OrdinalPosition, count(pcp_phone) as NumWithVal, (count(pcp_phone) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'phone' as Col, 58 as OrdinalPosition, count(phone) as NumWithVal, (count(phone) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'landline' as Col, 59 as OrdinalPosition, count(landline) as NumWithVal,
(count(landline) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'email' as Col, 60 as OrdinalPosition,
count(email) as NumWithVal, (count(email) / CAST(355963 as decimal) * 100) as
Percnt FROM [patient_fact_detail] UNION SELECT 'ipa' as Col, 61 as
OrdinalPosition, count(ipa) as NumWithVal, (count(ipa) / CAST(355963 as decimal) *
100) as Percnt FROM [patient_fact_detail] UNION SELECT 'client_name' as Col, 62
as OrdinalPosition, count(client_name) as NumWithVal, (count(client_name) /
CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] UNION SELECT
'pcp_name' as Col, 63 as OrdinalPosition, count(pcp_name) as NumWithVal,
(count(pcp_name) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'reqCallbkDt' as Col, 64 as OrdinalPosition,
count(reqCallbkDt) as NumWithVal, (count(reqCallbkDt) / CAST(355963 as decimal) *
100) as Percnt FROM [patient_fact_detail] UNION SELECT 'appointment_datetime' as
Col, 65 as OrdinalPosition, count(appointment_datetime) as NumWithVal,
(count(appointment_datetime) / CAST(355963 as decimal) * 100) as Percnt FROM
[patient_fact_detail] UNION SELECT 'organization_name' as Col, 66 as
OrdinalPosition, count(organization_name) as NumWithVal, (count(organization_name)
/ CAST(355963 as decimal) * 100) as Percnt FROM [patient_fact_detail] ORDER BY 3
DESC, OrdinalPosition
---------------
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @tot INT;
SELECT @tot = COUNT(*) FROM app.patient_fact_detail
select @tot
SELECT @sql += ' UNION ' + CHAR(13) + CHAR(10)
+ 'SELECT DISTINCT '
+ '''' + COLUMN_NAME + ''' as Col,count( name) from sys.procedures where '
+ ' OBJECT_DEFINITION(OBJECT_ID) LIKE ''%patient_fact_detail%'' AND
OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'+COLUMN_NAME+'%'' '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'patient_fact_detail';
SELECT @sql = SUBSTRING(@sql, CHARINDEX(char(10), @sql), LEN(@sql)) -- remove
first line (extra union)
SELECT @sql += CHAR(13) + CHAR(10) + 'ORDER BY 3 DESC, OrdinalPosition'
select @sql
