Parameters from Stored procedures Inside SSRS

1
votes

I am trying to use SQL Server Reporting Studio (SSRS) to create reports that allow user to choose a database on sql server and based on the choosen databases information about tables on the database will be displayed.

For example if the user picks master database; all tables, table size, number of rows, columns will be retrieved about this table. I am thinking of using the stored procedure ms_foreachdb but I am not sure how to build the parameter on SSRS to let users choose the database name.

Any idea of how to create the stored procedure that allow the user to retrieve table info from a database name of user choice?

1
sqlsql-serverreporting-servicesssrs-2008

1 Answers

1
votes

You can use something like:

select database_id, name from sys.databases

to populate your parameter.

Once you have the parameter populated, you can pass whatever is selected into your stored procedure as per any other SSRS stored procedure based DataSet.

You could use sys.tables or any other the other system views to get the metadata you require.

More information:

sys.databases

sys.tables