SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_desc


sp_desc

Author
Message
sergey.benner
sergey.benner
Old Hand
Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)

Group: General Forum Members
Points: 308 Visits: 164
Comments posted to this topic are about the item sp_desc
hidasizs
hidasizs
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search