Microsoft SQL 2016/13.0: What I'm attempting to do is add a COUNT(*) to this query. What this query does - It finds the tables of a common naming convention (many tables with a common prefix) and out of those tables - what tables have the specific column name. So far so good - it works! What I need next is, within these tables, are there any "rows" (of data) and if so, how many rows in each table. Thanks in advance for helping out, greatly appreciated!. Cheers!
IF OBJECT_ID('tempdb..#result') IS NOT NULL
DROP TABLE #result
GO
CREATE TABLE #result(
DatabaseName [nvarchar](128),
TableName [nvarchar](128),
ColumnName [nvarchar](128),
--NumOfRows int NOT NULL, -- COUNT COLUMN
)
GO
EXEC RunOnAllDataBases'
SELECT
{DatabaseName},
t.name AS TableName,
c.name AS ColumnName
--COUNT(*) AS NumOfRows --COUNT THE ROWS
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ''SPECIFIC COLUMN NAME''
AND (t.name LIKE ''COMMON_TABLE_NAME_%')
{ - SOMEWHERE IN HERE I NEED THE COUNT QUERY - }
ORDER BY TableName;
',
'INSERT into #result';
SELECT * FROM #result
--WHERE NumOfRows > 0
--ORDER BY NumOfRows DESC
IF OBJECT_ID('tempdb..#result') IS NOT NULL
DROP TABLE #result
GO
Here is what became the solution (there is one error - Must declare the scalar variable - not sure how to fix, although if anyone sees where, please feel free to comment) - very much appreciated. Cheers!
IF OBJECT_ID('tempdb..#result') IS NOT NULL
DROP TABLE #result
GO
CREATE TABLE #result (
[EnterpriseID] [uniqueidentifier],
[EnterpriseName] nvarchar](100),
MY_COUNT_COLUMN int,
MyApp_TableName [nvarchar](500)
)
GO
EXEC RunOnAllDataBases'
DECLARE @myresults table (
[EnterpriseID] [uniqueidentifier],
[EnterpriseName] [nvarchar](100),
MY_COUNT_COLUMN int,
MyApp_TableName [nvarchar](500)
)
DECLARE @MyApp_TableName nvarchar(500)
DECLARE qrylist CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT t.name AS MyApp_TableName
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ''SPECIFIC COLUMN NAME''
AND (t.name LIKE ''COMMON_TABLE_NAME_%')
ORDER BY MyApp_TableName;
OPEN qrylist
FETCH NEXT FROM qrylist
INTO @MyApp_TableName
WHILE @@FETCH_STATUS = 0
BEGIN
print @MyApp_TableName
declare @cnt int = 0;
exec sp_executesql N''@MyApp_TableName'',
N''@rowcount int output'', @cnt output;
INSERT into @myresults (
[EnterpriseID],
[EnterpriseName],
MY_COUNT_COLUMN,
MyApp_TableName
)
SELECT {entid},
{dbname},
@cnt,
@MyApp_TableName
FETCH NEXT FROM qrylist INTO @MyApp_TableName
END
CLOSE qrylist
DEALLOCATE qrylist
SELECT * from @myresults',
'INSERT into #result';
SELECT * FROM #result
IF OBJECT_ID('tempdb..#result') IS NOT NULL
DROP TABLE #result
GO
