CODEWITHSUNDEEP
c#asp.netsql-server
user11298131
user11298131

Reputation:

For Each Loop to iterate all the tables name(SQL)

I have a database with similar tables. Each table are named per year for example, 1997, 1998 till 2019. Each year a new table will be added with the corresponding year name. I am currently using the below code to get the count of a particular value in a table.

The below code are called inside a for loop in an ASP.NET app that has the years stored in an array. My concern is right now each year I will have to update the array in the ASP.NET for the new year table added.

Is there a way that I can all the tables name in the database and pass it as parameter in the below code.

(
  @TableName SysName,
  @SearchInput NVARCHAR(50),
  @SearchParam NVARCHAR(50)
)
AS 
  SET NOCOUNT ON;

  DECLARE @SQL NVARCHAR(MAX) = N'SELECT COUNT(' +
                               QUOTENAME(@SearchParam) +
                               N') FROM ' +
                               QUOTENAME(@TableName) +
                               N' WHERE ' +
                               QUOTENAME(@SearchParam) +
                               N' LIKE ' + 
                               QUOTENAME(@SearchInput, '''') +
                               N';';

EXEC    @return_value = [dbo].[CountSP]
        @TableName = N'1999',
        @SearchParam = N'USERDESC',
        @SearchInput = N'%lol%'

Upvotes: 1

Views: 341

Answers (2)

Mohammad
Mohammad

Reputation: 1990

You can query system views to get all available table names. With some filtering you can restrict it to return only these year tables in case your database has other tables you want to skip.

SELECT DISTINCT name FROM sys.tables WHERE name LIKE 'Year%'

Assuming your tables are named: Year2000, Year2001, ....etc. You can edit it as needed.

Upvotes: 1

FeRaaC
FeRaaC

Reputation: 486

If you only have these tables in your database then you can use

DataTable dt = dbConnection.GetSchema("Table");

to get all the tables and use

 foreach (DataRow dr in dt) {
    string tableName = (string) dr[2];
    /* other code */
}

to iterate through them. If you have other tables in your database as well you might sort them out by checking if the table name fullfills a 4 number regex or something.

Upvotes: 2

Related Questions