[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 國際 授權條款授權,文章歡迎轉載,請註明出處,謝謝~~~