Post reply

Help with creating a parameterized Stored Procedure which takes Server Name, DB Name and Table Name and gets metadata from information_schema

  • December 24, 2018 at 11:56 am

    #373365

    Hello All,
    I needed help with the below query. I was trying to create a parameterized proc that takes Server Name, DB Name and Table Name and returns metadata from information_schema.columns.
    Thanks for your support!

    CREATE PROC GetMetadata @ServerName varchar(25), @DbName varchar(25), @TableName varchar(25)
    AS
    SET NOCOUNT ON;

    SET @ServerName = (
                        SELECT NAME FROM sys.servers
                        WHERE server_id = 0
                     );
    SET @DbName = (
                        SELECT NAME FROM sys.databases
                 );
    SELECT  TABLE_NAME,
             COLUMN_NAME,
             DATA_TYPE
    FROM  [@ServerName].[@DbName].INFORMATION_SCHEMA.COLUMNS
    WHERE  TABLE_CATALOG = @DbName AND
        TABLE_NAME = @TableName

    EXEC GetMetadata '@ServerName', '@DbName', '@TableName'

  • December 26, 2018 at 8:11 am

    #2017324

    I'm not sure why you need ServerName, since you could have potential issues with linked servers.  To get the info for each db, you can use sp_MSforeachdb.  Something like

    declare @command nvarchar(max) = 'Use ?; select * from information_Schema.Columns'

    exec sp_MSforeachdb @command1 = @Command

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply