asked on

Running a query on every table in a database

Instead of running this query on one table at a time, how could it be changed to run on every table in a database? The table name in this query below is called [MembershipFeeds]:

SELECT 'SELECT '''+c.Table_Name+''' Table_Name, '''+c.Column_Name+''' Column_Name, '

+CASE WHEN c.Data_Type LIKE '%DATE%'

THEN 'CONVERT(VARCHAR(8),'+c.Column_Name+',112)'

WHEN c.Data_Type LIKE '%INT' OR c.Data_Type IN ('DECIMAL','NUMERIC')

THEN 'CAST('+c.Column_Name+' AS VARCHAR)'

ELSE c.Column_Name

END

+' Column_Val, COUNT(1) QA_Freq_Cnt, CAST(CAST(COUNT(1) AS NUMERIC)/CAST(@RowCnt AS NUMERIC) AS NUMERIC(7,4)) QA_Freq_Pct'

+' FROM ['+t.Table_Schema+'].'+t.Table_Name

+' GROUP BY '+c.Column_Name

+' UNION ALL'

-- , ''''+column_name+''','

FROM INFORMATION_SCHEMA.Tables t

JOIN INFORMATION_SCHEMA.Columns c

ON t.Table_Catalog=c.Table_Catalog

AND t.Table_Schema=c.Table_Schema

AND t.Table_Name=c.Table_Name

-- AND c.Column_Name IN ('Cms_Contract_Id','Cms_Esrd_Ind','Cms_Hospice_Ind','Cms_Lis_Copay_Category','Cms_Medicaid_Ind','Gender'

-- ,'Group_Id','Group_Indicator','Home_State','Insured_plan_Id','Lang_Id','Mailto_State'

-- ,'Pbp_Id','Plan_Id','PW_Option','SRC_SYS_SRCID','Termination_Cd')

WHERE t.Table_Name IN ('MembershipFeeds')

ORDER BY t.Table_Name,c.Column_Name

Scott Pletcher

The SQL as written cannot run successfully because "Column_Val" is not included in the GROUP BY nor does it have an aggregate function used against it, such as MAX().

Currently I think the code will give you the well-known error:
"
Column <column_name> is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
"

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial