April 13, 2012

sp_adddatatype (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_adddatatype(nvarchar @dbms
, nvarchar @version
, nvarchar @type
, int @createparams)

MetaData:

   
--
-- Name:
-- sp_adddatatype
--
-- Description:
-- Add DBMS data type as an available type
--
-- Returns:
-- 0 if successful
-- 1 if failed
--
-- Security:
-- public
--
-- Notes:
-- Implicitly will create a DBMS if it doesn't currently exist.
--

CREATE PROCEDURE sys.sp_adddatatype
(
@dbms sysname,
@version sysname = NULL,
@type sysname,
@createparams int = 0
)
AS
BEGIN
DECLARE @dbms_id int

SET NOCOUNT ON

--
-- Security Check: require sysadmin
--
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,16,-1)
RETURN (1)
END

-- Prepare dbms for case insensitive searches
SET @dbms = UPPER(@dbms)

BEGIN TRAN
SAVE TRAN adddatatype

-- Get DBMS id
SELECT @dbms_id = dbms_id
FROM msdb.dbo.MSdbms
WHERE dbms = @dbms
AND version = @version

IF @dbms_id IS NULL OR @@ERROR <> 0
BEGIN
-- Add new DBMS
INSERT INTO msdb.dbo.MSdbms (dbms, version)
VALUES (@dbms, @version)

IF @@ERROR <> 0
BEGIN
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION adddatatype
COMMIT TRAN
END
RETURN (1)
END

SELECT @dbms_id = IDENT_CURRENT('msdb.dbo.MSdbms')
END

-- Verify data type doesn't already exist
IF EXISTS
(
SELECT datatype_id
FROM msdb.dbo.MSdbms_datatype
WHERE dbms_id = @dbms_id
AND UPPER(type COLLATE DATABASE_DEFAULT) =
UPPER(@type) COLLATE DATABASE_DEFAULT
)
BEGIN
RAISERROR (21655, 16, -1, @type)

IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION adddatatype
COMMIT TRAN
END
RETURN (1)
END

-- Add data type
INSERT INTO msdb.dbo.MSdbms_datatype (dbms_id, type, createparams)
VALUES (@dbms_id, @type, @createparams)

IF @@ERROR <> 0
BEGIN
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION adddatatype
COMMIT TRAN
END
RETURN (1)
END

COMMIT TRAN
RETURN (0)
END

No comments:

Post a Comment

Total Pageviews