在sql的数据库中为所有表运行相同的命令

问题描述

大家好，
任何人都可以告诉我如何对数据库的所有表执行相同的查询.我已经拥有通过查询检索到的所有表的名称，即

Hi guys,
Can anyone please tell me that how can i execute a same query for all the tables of a database.I am already having the names of all tables retrieved by a query i.e

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE''

查询数据库是它必须检查所有表的条件(其中day为Monday，period4为null).

请尽快帮助我，因为这是我项目的一部分.
问候，
Akkywadhwa

Query for database is that it must check the condition(where day is Monday and period4 is null) for all tables.

Please help me as soon as it could be possible as it a part of my project.
Regards,
Akkywadhwa

推荐答案

CREATE TABLE #DBTables (Name NVARCHAR(256), ID INT IDENTITY)

INSERT #DBTables
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

DECLARE @Count INT, @RC INT
SET @RC = @@ROWCOUNT
SET @Count = 1

WHILE (@Count < @RC)
BEGIN
    DECLARE @TableName NVARCHAR(256)

    SELECT @TableName = Name FROM #DBTables WHERE ID = @Count

    DECLARE @Query NVARCHAR(MAX)

    SET @Query = 'SELECT * FROM ' + @TableName + ' WHERE Day = ''Monday'' AND Period4 = NULL'

    EXEC(@Query)

    SET @Count = @Count + 1
END

DROP TABLE #DBTables

using (
              SqlConnection con =
                  new SqlConnection(ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionString))
       {
           using(SqlCommand cmd = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'",con))
           {
               DataTable dt = new DataTable();
               SqlDataAdapter adpt = new SqlDataAdapter(cmd);
               adpt.Fill(dt);
               foreach (DataRow row in dt.Rows)
               {
                      //Here put your condtions for cmd2
                   using (SqlCommand cmd2 = new SqlCommand("select * from " + row[0], con)) ;
                   {
                       SqlDataAdapter adpt2 = new SqlDataAdapter(cmd);
                       DataTable dt2 = new DataTable();
                       adpt2.Fill(dt2);
                   }
               }
           }
       }

最好的问候
米特瓦里(M.Mitwalli)

Best Regards
M.Mitwalli

这篇关于在sql的数据库中为所有表运行相同的命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！