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
JGH5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
"
0
lcohanDatabase 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;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.