I have the below query that shows a list of tables and number of rows and total space in GB. This query works really well, however I would like to create a stored procedure whereby I can add a parameter to choose what database on the same server to query?
Currently this query would only work based on the current database that I am in. Basically the premise is that I have a simple application where an admin can change the database name via a dropdown and that will show the below queries results bases on the db name selected.
I don't know how to create a stored proc without hardcoding an if statement to set the 'USE' statement. I would rather avoid dynamic sql if that makes sense but Google seems to be pointing that way. If I have to then so be it:)
I would kindly appreciate any assistance in helping me resolve this issue?
SELECT s.name + '.' + t.name AS [Table Name], part.rows AS [Total Rows In Table - Modified], CAST(SUM(DISTINCT au.total_pages)
* 8 / 1024.000 / 1024.000 AS NUMERIC(18, 3)) AS [Table's Total Space In GB]
FROM sys.tables AS t INNER JOIN
sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN
sys.indexes AS idx ON t.object_id = idx.object_id INNER JOIN
sys.partitions AS part ON idx.object_id = part.object_id AND idx.index_id = part.index_id INNER JOIN
sys.allocation_units AS au ON part.partition_id = au.container_id INNER JOIN
sys.filegroups AS fGrp ON idx.data_space_id = fGrp.data_space_id INNER JOIN
sys.database_files AS Df ON Df.data_space_id = fGrp.data_space_id
WHERE (t.is_ms_shipped = 0) AND (idx.object_id > 255)
GROUP BY t.name, s.name, part.rows
ORDER BY [Table's Total Space In GB] DESC