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

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

Open in new window

databarracksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Not hugely encouraged by anybody, but here is what you are looking for.
databarracksAuthor Commented:
Hi Nitin,

Thanks for that, however as you explicitly mentioned if it is not encouraged then I won't bother :(. Thanks for the tip so I can safely conclude that it isn't possible then?
Vitor MontalvãoMSSQL Senior EngineerCommented:
A stored procedure is part of a database, so or you create it in the master database and use a dynamic query where you can change the database by using the USE command or you can have the same stored procedure replicated to every database and then you can call it like this: 
EXEC database.dbo.sp_name

Open in new window

Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Download eBook
Nitin SontakkeDeveloperCommented:
It is definitely possible. Did you refer to the hyperlink I posted. All I was mentioning what is written at the end of that article.

It should be noted that adding objects to the master database is not generally considered a good practice, so I don't make use of this feature on production servers.
databarracksAuthor Commented:
Hi Vitor,

I did think of that approach but would you be kind as to elaborate on how I could declare the DB name in the stored proc within the master database?
databarracksAuthor Commented:
Ahh I see what you mean Nitin.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Personally, I don't have nothing against creating objects in master database. Especially if they intended to perform generic tasks.
What most of DBAs do, is to creating their private database in all SQL Server instances that they manage so they can use it for this kind of stuffs that will help with their daily tasks.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I did think of that approach but would you be kind as to elaborate on how I could declare the DB name in the stored proc within the master database?
It's only creating a dynamic SQL:
CREATE PROC sp_getFileSizes (@DB_Name AS system)
AS

DECLARE @MySQL VARCHAR(MAX)

SET @MySQL = 'USE ' + @DB_Name + '

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 [Tables 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 [Tables Total Space In GB] DESC'

EXEC @MySQL

Open in new window

databarracksAuthor Commented:
Hi Vitor,

Thanks for the stored proc, I am getting an error on the parameter variale @DB_Name has  an invalid type. I am guessing it should be a VARCHAR(256)??
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, didn't test it. Here's the correct version:
CREATE PROC sp_getFileSizes (@DB_Name AS VARCHAR(MAX))
AS

DECLARE @MySQL VARCHAR(MAX)

SET @MySQL = 'USE ' + @DB_Name + '

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 [Tables 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 [Tables Total Space In GB] DESC'

EXEC(@MySQL)
GO

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
databarracksAuthor Commented:
Do you know why I cannot see the sp in my viewer on SSMS?
Vitor MontalvãoMSSQL Senior EngineerCommented:
You might need to refresh the SSMS.
Nitin SontakkeDeveloperCommented:
@databarracks, however, from your original post, I gather you were averse to dynamic sql. Not too keen to use it if it is the only option available.
databarracksAuthor Commented:
Yes I was, what I was trying to say is that if it was the only option then I would use it..hence 'If I have to then so be it:)'
databarracksAuthor Commented:
Again this is something that is only used by 3 people within the organisation nothing public about it
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can't see another way besides the ones I've explained.
databarracksAuthor Commented:
I am happy with it and have created the sp in another database as means of 'best practises' because they say not to create new objects in the master db.
databarracksAuthor Commented:
Thank you to Vitor and Nitin for helping me out on this issue. Nitin assisted me in providing a useful link and Vitor provided the final solution of which I chose to use in my environment as it was a direct improvement of my original query
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Google

From novice to tech pro — start learning today.