How to Create Row Count Table and Continuous Columns
Hello SQL Server Central, I am going to do my best to explain what I need to accomplish. The application uses SQL Server database (SQL-2008R2 >) and currently has 1503 tables. I am in need of creating some SQL code that will create a Table and the first 2 columns. 1. TABLE NAME “dbo.AAA_TblRowCounts” 2. 1st Column Name “Table_Name” 3. 2nd column Name “row_count” I am a little new to SQL. I am using the below code to accomplish this. USE Mike_Test GO SELECT o.name AS 'Table_Name', ddps.row_count INTO dbo.AAA_TblRowCounts FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME GO This will give me every table and a row count for each table in the Mike_Test database. Now I need to create a new column. From what I find, use the “ALTER TABLE” to accomplish this. ALTER TABLE dbo.AAA_TblRowCounts ADD [5_row_count_01192015] bigint DEFAULT 0 not null The new column needs to have a specific name so we can track the row count changes when something is added to the application. The new row counts need to populate this new column by matching their Table Name to the first column named “Table_Name”. This is so we can see where the row counts change per table for each time this SQL code is run. I would like to have the new column name be “1_row_count_(CurrentDate in format = mmddyyyy)” EXAMPLE: New Column Name “1_row_count_01152015” Now another change is made to the Application and the need to run the code again. The new column name needs to increment by “ one(1) + _row_count__(CurrentDate in format = mmddyyyy)” I would like to have this 2nd new column named “2_row_count_(CurrentDate in format = mmddyyyy)”. EXAMPLE: New Column Name “2_row_count_01152015” The catch is I need this to loop through the Column names each time the code is run so it can increment the first number by one and continue the rest as described. I still want the number to increment by one no matter the date. Please see example screenshot below. At the end of all this I will be able to run queries against the table to see where counts have changed. Table_Name row_count 1_row_count_01162015 2_row_count_01162015 AAA_TblRowCounts 0 0 0 AC_ASSESSMENT 0 0 0 AC_ASSESSMENT_DET 0 0 0 AC_AUDIT_INFO 0 0 0 AC_EQUIP_DET 0 0 0 AC_EXEMPTIONS 0 0 0 AC_LEVY_MASTER 0 0 0 AC_LK_LEVY_EXEMPT 0 0 0 AC_MFG 0 0 0 AC_MODEL 0 0 0
This seems like a really poor way to hold this data. By converting the date dimension to a column name element you lose the ability to run any date based analysis of your row counts. Surely that's the point of tracking them? Also, what happens if new *tables* get added? I'd hold this data in a columnar format, then use SQL to present it in the way that you need. Admittedly, this is logically more complicated, but it feels worth it to me. -- setup simplified dummy data create table #row_counts (TableName varchar(20), CheckDate datetime, RowsInTable int) insert #row_counts values('Bob','1 January 2015',100) insert #row_counts values('Dave','1 January 2015',100) insert #row_counts values('Steve','1 January 2015',100) insert #row_counts values('Bob','1 February 2015',150) insert #row_counts values('Dave','1 February 2015',150) insert #row_counts values('Steve','1 February 2015',150) go -- we can add labels dynamically fairly easily: -- use dense_rank to add check numbers select CheckNumber = dense_rank() over (order by CheckDate) , * from #row_counts -- combine this with formatted date to generate labels select CheckNumber = dense_rank() over (order by CheckDate) , CheckLabel = cast(dense_rank() over (order by CheckDate) as varchar) + '_row_count_' + convert(varchar(8),CheckDate,112) , * from #row_counts -- we can then pivot to get columns select TableName , [1_row_count_20150101] , [2_row_count_20150201] from ( select TableName , CheckLabel = cast(dense_rank() over (order by CheckDate) as varchar) + '_row_count_' + convert(varchar(8),CheckDate,112) , RowsInTable from #row_counts ) src pivot ( sum(RowsInTable) for CheckLabel in ( [1_row_count_20150101],[2_row_count_20150201] ) ) pvt -- result: /* TableName 1_row_count_20150101 2_row_count_20150201 -------------------- -------------------- -------------------- Bob 100 150 Dave 100 150 Steve 100 150 */ -- BUT. doing this in reality is a bit trickier -- this is because we don't want to have to explicitly provide the CheckLabel names at runtime -- otherwise a script change would be required every time a new check was run -- to get round this, we can use a dynamic query -- this means that we don't need to make manual changes to scripts when the data changes -- declare a variable to hold the sql statement that we generate declare @sql varchar(2000) -- and another to hold the list of columns (i.e. CheckDates) to extract declare @cols varchar(1000) -- this query extracts the label values that we're interested in -- and converts them to a comma separated string select @cols = stuff((select distinct ',' + quotename(cast(dense_rank() over (order by CheckDate) as varchar) + '_row_count_' + convert(varchar(8),CheckDate,112)) from #row_counts -- you can add a where clause here if you just want a subset for xml path(''), type ).value('.', 'varchar(1000)') ,1,1,'') -- show result print '@cols:' print @cols -- we now use this to generate a pivot query, just like the one we had before -- but this time, we build it as a text string to execute later select @sql = 'select TableName , ' + @cols + ' from ( select TableName , CheckLabel = cast(dense_rank() over (order by CheckDate) as varchar) + ''_row_count_'' + convert(varchar(8),CheckDate,112) , RowsInTable from #row_counts ' -- add a where clause here (within the quoted block) if you added one above -- not absolutely required, but will reduce the set size, so a good idea + ') src pivot ( sum(RowsInTable) for CheckLabel in (' + @cols +') ) pvt' -- show result print '@sql:' print @sql -- execute the query exec (@sql) -- result: /* TableName 1_row_count_20150101 2_row_count_20150201 -------------------- -------------------- -------------------- Bob 100 150 Dave 100 150 Steve 100 150 */ Drop this into a stored procedure with start and end date inputs and you have a nice and simple, flexible way of extracting and presenting the data.
I generally don't recommend beginning column names with numbers because they do not follow the standard naming rules and therefore must be delimited with brackets or double quotes. I'm also not sure I understand why you're doing this, but I'm going to temporarily ignore all of that to directly answer your question. My suggestion is that the code name the columns based on the number of column already existing in the table. Also, given your naming convention and the possibility of more than 9 updates, I'm going to slightly alter the names to have 2-digit numbers. I am not including the schema because you did not, but I suggest adding that as another column if you have tables in more than just the dbo schema. DECLARE @sql nvarchar(200) = ( SELECT 'ALTER TABLE dbo.AAA_TblRowCounts ADD ' + QUOTENAME(RIGHT('00'+CONVERT(nvarchar(2), COUNT(*)-2+1), 2) + '_row_count_' + REPLACE(CONVERT(nvarchar(10), SYSDATETIME(), 101), '/', '')) + ' bigint DEFAULT 0 not null;' FROM sys.columns c WHERE c.object_id = OBJECT_ID('dbo.AAA_TblRowCounts') ); SELECT @sql; EXEC(@sql);