[MSSQL][procedure] 修改描述懶人包

2014-05-31

4832
0
T-SQL Scripts
2017-09-05

[procedure] 修改欄位描述

※ 2014-06-05 描述懶人包更新到 v 1.3 版：最主要的異動是因為 MSSQL 2000 與 2005+ 後續版本語法差異過大，分開寫比較好維護。

在 MSSQL，要修改描述會用到 sp_addextENDedproperty 和 sp_updateextENDedproperty

使用上要先判別描述是否存在，存在則 update，反之則 insert

再加上SQL 2000的 @level0type 使用上與 2005+ 的版本不太一樣

SQL 2000, SQL 2005：@level0type =N'user'

SQL 2005+：@level0type =N'schema'

每次寫語法都要判別有一點小小的不方便

這邊把它整理成 procedure

方便日後使用。

MSSQL 2000：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: v 1.3
-- MSSQL Version: 2000
-- Create date: 2014-05-29
-- Update date: 2014-06-05
-- Description:	修改描述(資料庫、結構、資料表、欄位)，不須先判別是否描述已存在
-- Require: none
-- =============================================
IF OBJECT_ID(N'usp_RefreshDescription') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_RefreshDescription];
GO
 
CREATE PROCEDURE [dbo].[usp_RefreshDescription]
	(@Description nvarchar(1000)
	, @SchemaName nvarchar(100) = NULL
	, @tableName nvarchar(100) = NULL
	, @columnName nvarchar(100) = NULL
	)
AS
DECLARE @schmaType nvarchar(10);
DECLARE @tableType nvarchar(10);
DECLARE @columnType nvarchar(10);
SET @schmaType = N'USER';
SET @tableType = N'TABLE';
SET @columnType = N'COLUMN';
 
--如果沒有傳入SCHEMA，後續參數(SCHEMA, TABLE, COLUMN)都設為空
IF (@SchemaName IS NULL)
BEGIN
	SET @schmaType = NULL;
	SET @tableType = NULL;
	SET @columnType = NULL;
	SET @SchemaName = NULL;
	SET @tableName = NULL;
	SET @columnName = NULL;
END
--如果沒有傳入table，後續參數(TABLE, COLUMN)都設為空
ELSE IF (@tableName IS NULL)
BEGIN
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = @SchemaName AND CATALOG_NAME = DB_NAME())
		RAISERROR ('查無此結構', -- Message text.
               16, -- Severity.
               1 -- State.
               );
	SET @tableType = NULL;
	SET @columnType = NULL;
	SET @tableName = NULL;
	SET @columnName = NULL;
END
--如果沒有傳入column，後續參數(COLUMN)都設為空
ELSE IF (@columnName IS NULL)
BEGIN
	IF OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@tableName)) IS NULL
		RAISERROR ('查無此資料表', -- Message text.
               16, -- Severity.
               1 -- State.
               );
	SET @columnType = NULL;
	SET @columnName = NULL;
END
ELSE
BEGIN
	IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@tableName)))
		RAISERROR ('查無此欄位', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END
 
--如果是改資料庫描述
IF (@SchemaName IS NULL)
BEGIN
	--檢查是否已存在描述(有:update, 無:insert)
	IF NOT EXISTS (
		SELECT * FROM ::fn_listextENDedproperty (default, default, default, default, default, default, default)
	) 
		EXEC sp_addextENDedproperty N'caption', @Description;
	ELSE 
		EXEC sp_updateextENDedproperty N'caption', @Description;
END
ELSE
BEGIN
	--檢查是否已存在描述(有:update, 無:insert)
	IF NOT EXISTS (
		SELECT * FROM ::fn_listextENDedproperty (default, @schmaType, @SchemaName, @tableType, @tableName, @columnType, @columnName)
	) 
	BEGIN 
		EXEC sp_addextENDedproperty N'caption', @Description
			, @schmaType, @SchemaName
			, @tableType, @tableName
			, @columnType, @columnName;
	END 
	ELSE 
	BEGIN 
		EXEC sp_updateextENDedproperty N'caption', @Description
			, @schmaType, @SchemaName
			, @tableType, @tableName
			, @columnType, @columnName;
	END
END
GO

MSSQL 2005+：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: v 1.3
-- MSSQL Version: 2005+
-- Create date: 2014-05-29
-- Update date: 2014-06-05
-- Description:	修改描述(資料庫、結構、資料表、欄位)，不須先判別是否描述已存在
-- Require: none
-- =============================================
IF OBJECT_ID(N'usp_RefreshDescription') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_RefreshDescription];
GO

CREATE PROCEDURE [dbo].[usp_RefreshDescription]
	(@Description nvarchar(1000)
	, @SchemaName nvarchar(100) = NULL
	, @tableName nvarchar(100) = NULL
	, @columnName nvarchar(100) = NULL
	)
AS
DECLARE @schmaType nvarchar(10) = N'SCHEMA';
DECLARE @tableType nvarchar(10) = N'TABLE';
DECLARE @columnType nvarchar(10) = N'COLUMN';

--如果沒有傳入SCHEMA，後續參數(SCHEMA, TABLE, COLUMN)都設為空
IF (@SchemaName IS NULL)
BEGIN
	SET @schmaType = NULL;
	SET @tableType = NULL;
	SET @columnType = NULL;
	SET @SchemaName = NULL;
	SET @tableName = NULL;
	SET @columnName = NULL;
END
--如果沒有傳入table，後續參數(TABLE, COLUMN)都設為空
ELSE IF (@tableName IS NULL)
BEGIN
	IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @SchemaName)
		RAISERROR ('查無此結構', -- Message text.
               16, -- Severity.
               1 -- State.
               );
	SET @tableType = NULL;
	SET @columnType = NULL;
	SET @tableName = NULL;
	SET @columnName = NULL;
END
--如果沒有傳入column，後續參數(COLUMN)都設為空
ELSE IF (@columnName IS NULL)
BEGIN
	IF OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@tableName)) IS NULL
		RAISERROR ('查無此資料表', -- Message text.
               16, -- Severity.
               1 -- State.
               );
	SET @columnType = NULL;
	SET @columnName = NULL;
END
ELSE
BEGIN
	IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@tableName)))
		RAISERROR ('查無此欄位', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END

--如果是改資料庫描述
IF (@SchemaName IS NULL)
BEGIN
	--檢查是否已存在描述(有:update, 無:insert)
	IF NOT EXISTS (
		SELECT * FROM ::fn_listextENDedproperty (default, default, default, default, default, default, default)
	) 
		EXEC sp_addextENDedproperty N'MS_Description', @Description;
	ELSE 
		EXEC sp_updateextENDedproperty N'MS_Description', @Description;
END
ELSE
BEGIN
	--檢查是否已存在描述(有:update, 無:insert)
	IF NOT EXISTS (
		SELECT * FROM ::fn_listextENDedproperty (default, @schmaType, @SchemaName, @tableType, @tableName, @columnType, @columnName)
	) 
	BEGIN 
		EXEC sp_addextENDedproperty N'MS_Description', @Description
			, @schmaType, @SchemaName
			, @tableType, @tableName
			, @columnType, @columnName;
	END 
	ELSE 
	BEGIN 
		EXEC sp_updateextENDedproperty N'MS_Description', @Description
			, @schmaType, @SchemaName
			, @tableType, @tableName
			, @columnType, @columnName;
	END
END
GO

使用範例：

--更新資料庫(DBName)描述：
USE DBName;
GO
EXEC [dbo].[usp_RefreshDescription] '資料庫描述';

--更新結構(dbo)描述 (僅支援 MSSQL 2005+)：
USE DBName;
GO
EXEC [dbo].[usp_RefreshDescription] '結構描述', 'dbo';

--更新資料表(dbo.MyTable)描述：
USE DBName;
GO
EXEC [dbo].[usp_RefreshDescription] '資料表描述', 'dbo', 'MyTable';

--更新欄位(dbo.MyTable.MyColumn)描述：
USE DBName;
GO
EXEC [dbo].[usp_RefreshDescription] '欄位描述', 'dbo', 'MyTable', 'MyColumn';

參考資料：

sys.fn_listextendedproperty (Transact-SQL)
sp_updateextendedproperty (Transact-SQL)
RAISERROR (Transact-SQL)
How do I query if a database schema exists

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~

SQL Server

回首頁

資料庫黑洞

Henry 的 SQL 學習筆記

[MSSQL][procedure] 修改描述懶人包

標籤雲

系列文章