sp_desc

• sergey.benner

  Ten Centuries

  Points: 1048

  More actions

  July 14, 2008 at 9:28 pm

  #381665

  Comments posted to this topic are about the item sp_desc

• hidasizs

  Newbie

  Points: 9

  More actions

  February 19, 2013 at 2:15 am

  #1589073

  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