sp_desc

  • Comments posted to this topic are about the item sp_desc

  • Hi Sergey,

    I wrote a stored procedure some weeks ago with similar aim like your sp_desc. Today - when I used it - reminds me to check the internet if there exists something like mine and "googled" it with the name of my stored procedure (which is the same like yours anyway :-)) and found yours. As my original purpose was to create an sql*plus like DESC[RIPTION] command, my version gives almost the same result as the sql*plus DESC. I think it can be useful for those whom have got used to work in sql*plus and want to find similar output. I want to share it with others but don't know where, so I choosed your topic and shared it here. I hope you don't mind it.

    USE master;

    GO

    -- NOTES

    -- in case if stored procedure will NOT mark as system object (see the end of the code)

    -- you should use schema name in front of object name at the beginning of this code

    -- (drop and create section)

    IF OBJECT_ID('sp_desc', 'P') IS NOT NULL

    DROP PROCEDURE sp_desc

    GO

    CREATE PROCEDURE sp_desc (

    @tableName nvarchar(128)

    ) AS

    BEGIN

    DECLARE @dbName sysname;

    DECLARE @schemaName sysname;

    DECLARE @objectName sysname;

    DECLARE @objectID int;

    DECLARE @tmpTableName varchar(100);

    DECLARE @sqlCmd nvarchar(4000);

    SELECT @dbName = PARSENAME(@tableName, 3);

    IF @dbName IS NULL SELECT @dbName = DB_NAME();

    SELECT @schemaName = PARSENAME(@tableName, 2);

    IF @schemaName IS NULL SELECT @schemaName = SCHEMA_NAME();

    SELECT @objectName = PARSENAME(@tableName, 1);

    IF @objectName IS NULL

    BEGIN

    PRINT 'Object is missing from your function call!';

    RETURN;

    END;

    SELECT @objectID = OBJECT_ID(@dbName + '.' + @schemaName + '.' + @objectName);

    IF @objectID IS NULL

    BEGIN

    PRINT 'Object [' + @dbName + '].[' + @schemaName + '].[' + @objectName + '] does not exist!';

    RETURN;

    END;

    SELECT @tmpTableName = '#tmp_DESC_' + CAST(@@SPID AS VARCHAR) + REPLACE(REPLACE(REPLACE(REPLACE(CAST(CONVERT(CHAR, GETDATE(), 121) AS VARCHAR), '-', ''), ' ', ''), ':', ''), '.', '');

    --PRINT @tmpTableName;

    SET @sqlCmd = '

    USE ' + @dbName + '

    CREATE TABLE ' + @tmpTableName + ' (

    [NAME] nvarchar(128) NOT NULL

    ,[TYPE] varchar(50)

    ,[CHARSET] varchar(50)

    ,[COLLATION] varchar(50)

    ,[NULLABLE] varchar(3)

    ,[DEFAULT] nvarchar(4000)

    ,[COMMENTS] nvarchar(3750));

    INSERT INTO ' + @tmpTableName + '

    SELECT

    a.[NAME]

    ,a.[TYPE]

    ,a.[CHARSET]

    ,a.[COLLATION]

    ,a.[NULLABLE]

    ,a.[DEFAULT]

    ,b.[COMMENTS]

    FROM

    (

    SELECT

    COLUMN_NAME AS [NAME]

    ,CASE DATA_TYPE

    WHEN ''char'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''

    WHEN ''numeric'' THEN DATA_TYPE + ''('' + CAST(NUMERIC_PRECISION AS VARCHAR) + '', '' + CAST(NUMERIC_SCALE AS VARCHAR) + '')''

    WHEN ''nvarchar'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''

    WHEN ''varbinary'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''

    WHEN ''varchar'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''

    ELSE DATA_TYPE

    END AS [TYPE]

    ,CHARACTER_SET_NAME AS [CHARSET]

    ,COLLATION_NAME AS [COLLATION]

    ,IS_NULLABLE AS [NULLABLE]

    ,COLUMN_DEFAULT AS [DEFAULT]

    ,ORDINAL_POSITION

    FROM

    INFORMATION_SCHEMA.COLUMNS

    WHERE

    TABLE_NAME = ''' + @objectName + '''

    ) a

    FULL JOIN

    (

    SELECT

    CAST(value AS NVARCHAR) AS [COMMENTS]

    ,CAST(objname AS NVARCHAR) AS [NAME]

    FROM

    ::fn_listextendedproperty (''MS_Description'', ''user'', ''' + @schemaName + ''', ''table'', ''' + @objectName + ''', ''column'', default)

    ) b

    ON a.NAME COLLATE Hungarian_CI_AS = b.NAME COLLATE Hungarian_CI_AS

    ORDER BY

    a.[ORDINAL_POSITION];

    SELECT * FROM ' + @tmpTableName + ';'

    --PRINT @sqlCmd;

    EXEC sp_executesql @sqlCmd;

    RETURN;

    END;

    GO

    -- mark as system object (must NOT indicate the dbo schema!!!)

    EXEC sys.sp_MS_marksystemobject sp_desc

    GO

    -- grant (just in case if procedure is NOT MARKED AS SYSTEM OBJECT)

    -- GRANT EXEC ON dbo.sp_desc TO PUBLIC;

    -- GO

    -- execute (just in case if procedure is NOT MARKED AS SYSTEM OBJECT)

    -- EXEC dbo.sp_desc 'RUMI.dbo.Companies';

    -- GO

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

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