Generic SQL Server Stored Procedure to display Table Names of any Database passed to it

I was recently playing around with a generic stored procedures that can list the table names and stored procedure of any database name that is passed to it. Here's the procedure

CREATE PROCEDURE [dbo].[usersp_GetTablesSP]

@DatabaseName VARCHAR(50)

AS

BEGIN

DECLARE @DBSql NVARCHAR(200)

SET @DBSql = 'USE '+ @DatabaseName +' ; SELECT * FROM Sys.Objects WHERE Type IN (''U'', ''P'') ORDER BY type_desc'

 

EXEC sp_executesql @DBSql

END

To run it -

EXEC usersp_GetTablesSP 'Pubs'

or

EXEC usersp_GetTablesSP 'Northwind'

Running the procedure displays the list of stored procedure and tables in that database.