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


      +' 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+''','



    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
Who is Participating?
lcohanConnect With a Mentor Database AnalystCommented:
Sorry I missed those policies under the term of use (just wanted to help nothing else) and please see the answer revised below - hope this is ok and it will help you after fixing your SQL code as ScottPletcher mentioned:

This is a example to use msforeachtable system stored proc to run some piece of code against each table in a database. I use the example below to get the space used for each table and all you need if to replace your SQL in the example below:

exec ('sp_MSforeachtable @command1="print ''?''", @command2="sp_spaceused ''?''" ')

For instance this is how you can capture those results in a table:

create table #TableUsed (
      tabName varchar(100),
      rowcnt int,
      reserved varchar(50),
      data varchar(50),
      index_size varchar(50),
      unused varchar(50));

insert into #TableUsed exec ('sp_MSforeachtable @command1="print ''?''", @command2="sp_spaceused ''?''" ');
select * from #TableUsed;
Scott PletcherSenior DBACommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.