I have MS SQL 2012 database with ~80 tables. Each table has a column UserID that identifies the user who created or last edited the record. I would like to write the sql statement that would give me the number of created/edited records for some userID in all database tables.
For example, user with UserID = 1 is the author of 3 records in Table1 and author of 2 records in Table2. Sql statement would need to give me a result like this:
How to do it? Thanks.
I doubt "EXEC sp_MSForEachTable" solution will not work, assuming the database has few more table, which don't have that User Id column, unless you are explicitly handling such failure using try catch block. In that case It will surely fail.
Here the solution to consider only those table which have the required column.
--To get the List of Table having the required column and Storing them into Temp Table.
Select ID = IDENTITY(Int,1,1),Object_Name(object_id) As TableName Into #ReqTables
From sys.columns where name = 'Crets'
--Creating Table to Store Row count result.
Create Table #RowCounts
(
Row_Count Int
, UserID Int
)
--Declaring variables
Declare @min Int,@max int,@TableName Nvarchar(255)
--get min and Max values from Required table for looping
Select @min = Min(Id),@max = Max(ID) From #ReqTables
--loop through Min and Max
While(@min <= @max)
BEgin
--get the table for a given loop Counter
Select @TableName = tableName From #ReqTables Where Id = @min
--Executing the Dynamic SQl
Exec ('Insert Into #RowCounts (Row_Count,UserID) Select Count(*),UserID From ' + @TableName + ' Group by UserID')
--incrementing the Counter
Set @min = @min + 1
End
Try this, it builds a dynamic query for all tables in your database and then executes it:
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = '(SELECT COUNT(*) FROM QUOTENAME(TABLE_NAME) WHERE user_id = 1) +'
FROM <DATABASE_NAME>.INFORMATION_SCHEMA.TABLES
SELECT @sql = 'SELECT' + LEFT(@sql, LEN(@sql) - 1) -- remove the last +
--PRINT(@sql) -- we may want to use PRINT to debug the SQL
EXEC(@sql)
Type your database name instead of <DATABASE_NAME> before you run it.
SELECT, try it now.If you are using the MS-SQL database and all the tables have UserId (int) as the common column, then you can use this query to get your result - please, try this one:
CREATE TABLE #counts
(
row_count INT,
UserID INT
)
EXEC sp_MSForEachTable @command1='INSERT #counts (row_count,UserId) SELECT COUNT(*),UserId FROM ? GROUP BY UserID '
SELECT SUM(row_count),userid FROM #counts GROUP BY UserID
DROP TABLE #counts
Thanks!
SUM()notGROUP BY.... But thenSUM()usesGROUP BY, so you're ultimately right :-)