troubleshooting Question

SQL Server show list of tables from a database with a parameter for the Database name

Avatar of databarracks
databarracks asked on
Microsoft SQL ServerGoogleSQL
18 Comments2 Solutions169 ViewsLast Modified:
Hi Guys,

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
ASKER CERTIFIED SOLUTION
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 2 Answers and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros