Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_desc Expand / Collapse
Author
Message
Posted Monday, July 14, 2008 9:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:07 AM
Points: 210, Visits: 160
Comments posted to this topic are about the item sp_desc
Post #534008
Posted Tuesday, February 19, 2013 2:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:19 AM
Points: 1, Visits: 26
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

Post #1421482
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse