我有一个脚本，它会添加一些扩展属性，其中一些描述了表格，另一些描述了列。如何检查扩展属性是否存在，以便在添加之前防止脚本报错？
9个回答
68
这个脚本首先检查表格描述的扩展属性是否存在：
IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('Table_Name') AND [name] = N'MS_Description' AND [minor_id] = 0)
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'This table is responsible for holding information.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Table_Name';
这个第二个脚本检查该列是否存在描述该列的扩展属性：
IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('Table_Name') AND [name] = N'MS_Description' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = 'Column_Name' AND [object_id] = OBJECT_ID('Table_Name')))
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'This column is responsible for holding information for table Table_Name.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Table_Name', @level2type = N'COLUMN', @level2name = N'Column_Name';
- ScubaSteve
2
3通常情况下，至少在历史上，只要问题或答案有价值，Stack Overflow就允许这样做。我相信他们会要求你在接受自己的答案之前留出一些时间，以便其他人也可以回答，并且你必须客观地选择最佳答案，而不是仅标记自己的答案以获取加分声望。所以这是可以的，唯一的问题是有些人在接受自己的答案之前没有给别人留足时间来回答。 - DavidScherer
重要提示：如果您在同一数据库中但不同模式下有两个具有相同名称的表，则该“if”条件将失败。我正在尝试找到一种指定该“IF”条件模式的方法（我不是SQL Server的高级用户），如果我找到了，我会在这里分享。 - sdlins
8
这里有另一种存储过程方法，类似于Ruslan K.的方法，但不涉及try/catch或显式事务：
-- simplify syntax for maintaining data dictionary
IF OBJECT_ID ('dbo.usp_addorupdatedescription', 'P') IS NOT NULL
DROP PROCEDURE dbo.usp_addorupdatedescription;
GO
CREATE PROCEDURE usp_addorupdatedescription
@table nvarchar(128), -- table name
@column nvarchar(128), -- column name, NULL if description for table
@descr sql_variant -- description text
AS
BEGIN
SET NOCOUNT ON;
IF @column IS NOT NULL
IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@table) AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id]
FROM SYS.COLUMNS WHERE [name] = @column AND [object_id] = OBJECT_ID(@table)))
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = @descr,
@level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
@level1name = @table, @level2type = N'COLUMN', @level2name = @column;
ELSE
EXECUTE sp_updateextendedproperty @name = N'MS_Description',
@value = @descr, @level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = @table,
@level2type = N'COLUMN', @level2name = @column;
ELSE
IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@table) AND [name] = N'MS_Description'
AND [minor_id] = 0)
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = @descr,
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = @table;
ELSE
EXECUTE sp_updateextendedproperty @name = N'MS_Description', @value = @descr,
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = @table;
END
GO
- Brian Westrich
5
我编写了一个简单的存储过程，用于添加或更新扩展属性“MS_Description”：
IF OBJECT_ID ('dbo.usp_addorupdatedescription', 'P') IS NOT NULL
DROP PROCEDURE dbo.usp_addorupdatedescription;
GO
CREATE PROCEDURE usp_addorupdatedescription
@table nvarchar(128), -- table name
@column nvarchar(128), -- column name, NULL if description for table
@descr sql_variant -- description text
AS
BEGIN
SET NOCOUNT ON;
DECLARE @c nvarchar(128) = NULL;
IF @column IS NOT NULL
SET @c = N'COLUMN';
BEGIN TRY
EXECUTE sp_updateextendedproperty N'MS_Description', @descr, N'SCHEMA', N'dbo', N'TABLE', @table, @c, @column;
END TRY
BEGIN CATCH
EXECUTE sp_addextendedproperty N'MS_Description', @descr, N'SCHEMA', N'dbo', N'TABLE', @table, @c, @column;
END CATCH;
END
GO
- Ruslan K.
4
我喜欢这个方法，但在执行此查询后遇到了开放事务的情况。
消息 266，级别 16，状态 2，过程 up_DataDict，行 178
EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句不匹配。前一个计数 = 0，当前计数 = 1。 - Damian我在下面发布了一种类似的方法，它不使用try/catch、事务回滚（不够优雅，但避免了Damian提出的问题）。 - Brian Westrich
3
也许我的回答与这个问题没有直接联系，但我想指出MS_Description实际上是区分大小写的，即使我们在SQL中添加它。如果我们使用MS_DESCRIPTION而不是MS_Description，则它将不会显示在SMSS表设计视图中。
在我的情况下，我必须像这样做才能删除现有描述并添加正确的描述。
在我的情况下，我必须像这样做才能删除现有描述并添加正确的描述。
IF EXISTS (
SELECT NULL
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID('TableName')
AND [name] = N'MS_DESCRIPTION'
AND [minor_id] = (
SELECT [column_id]
FROM SYS.COLUMNS
WHERE [name] = 'ColumnName'
AND [object_id] = OBJECT_ID('Tablename')
)
)
EXEC sys.sp_dropextendedproperty @name = N'MS_DESCRIPTION'
,@level0type = N'SCHEMA'
,@level0name = N'dbo'
,@level1type = N'TABLE'
,@level1name = N'TableName'
,@level2type = N'COLUMN'
,@level2name = N'ColumnName'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = N'Description detail'
,@level0type = N'SCHEMA'
,@level0name = N'dbo'
,@level1type = N'TABLE'
,@level1name = N'TableName'
,@level2type = N'COLUMN'
,@level2name = N'ColumnName'
GO
- Jansen
2
请查看我对Brian Westrich上面答案的扩展，但这个版本允许更新和添加任何扩展属性到表和列，而不仅仅是MS_Description。此外，它允许您在不同的数据库中使用存储过程添加和更新扩展属性，因此您只需要在服务器上拥有一个副本。
CREATE PROCEDURE dbo.AddOrUpdateExtendedProperty
@Database NVARCHAR(128) -- Database name
,@Schema NVARCHAR(128) -- Schema name
,@Table NVARCHAR(128) -- Table name
,@Column NVARCHAR(128) -- Column name, NULL if description for table
,@PropertyName NVARCHAR(128) -- Property name
,@PropertyValue SQL_VARIANT -- Property value
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NSQL NVARCHAR(MAX);
DECLARE @Level2Type NVARCHAR(128) = NULL;
DECLARE @Params NVARCHAR(MAX) = N'@Schema NVARCHAR(128), @Table NVARCHAR(128), @Column NVARCHAR(128), @PropertyName NVARCHAR(128), @PropertyValue SQL_VARIANT';
IF @Column IS NOT NULL
BEGIN
SET @NSQL = 'USE ' + @Database + ';
IF NOT EXISTS
(
SELECT NULL FROM sys.extended_properties
WHERE major_id = OBJECT_ID(''' + @Schema + '.' + @Table + ''')
AND name = @PropertyName
AND minor_id = (SELECT column_id
FROM sys.columns
WHERE name = @Column
AND object_id = OBJECT_ID(''' + @Schema + '.' + @Table + ''')
)
)
BEGIN
EXECUTE sp_addextendedproperty
@name = @PropertyName
,@value = @PropertyValue
,@level0type = N''SCHEMA''
,@level0name = @Schema
,@level1type = N''TABLE''
,@level1name = @Table
,@level2type = N''COLUMN''
,@level2name = @Column;
END
ELSE
BEGIN
EXECUTE sp_updateextendedproperty
@name = @PropertyName
,@value = @PropertyValue
,@level0type = N''SCHEMA''
,@level0name = @Schema
,@level1type = N''TABLE''
,@level1name = @Table
,@level2type = N''COLUMN''
,@level2name = @Column;
END
';
EXECUTE sp_executesql
@NSQL
,@Params
,@Schema
,@Table
,@Column
,@PropertyName
,@PropertyValue;
END
ELSE
BEGIN
SET @NSQL = 'USE ' + @Database + ';
IF NOT EXISTS
(
SELECT NULL
FROM sys.extended_properties
WHERE major_id = OBJECT_ID(''' + @Schema + '.' + @Table + ''')
AND name = @PropertyName
AND minor_id = 0
)
BEGIN
EXECUTE sp_addextendedproperty
@name = @PropertyName
,@value = @PropertyValue
,@level0type = N''SCHEMA''
,@level0name = @Schema
,@level1type = N''TABLE''
,@level1name = @Table;
END
ELSE
BEGIN
EXECUTE sp_updateextendedproperty
@name = @PropertyName
,@value = @PropertyValue
,@level0type = N''SCHEMA''
,@level0name = @Schema
,@level1type = N''TABLE''
,@level1name = @Table;
END
';
EXECUTE sp_executesql
@NSQL
,@Params
,@Schema
,@Table
,@Column
,@PropertyName
,@PropertyValue;
END
END
- corky_bantam
1
要检查给定表格可用的任何扩展属性，请按以下方式使用。
IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE [major_id] = OBJECT_ID('<schema>.<table_name>') AND [name] = N'MS_Description')
如果您的表有多个扩展属性，请将列ID作为
minor_id。IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE [major_id] = OBJECT_ID('<schema>.<table_name>') AND [name] = N'MS_Description' AND minor_id = 3)
查询 sys.extended_properties 目录视图以获取数据库中的所有扩展属性。
欲了解更多详情，请使用 http://msdn.microsoft.com/en-us/library/ms177541(v=sql.110).aspx。
- udayanga
0
为此，我使用fn_listextendedproperty函数，它允许指定模式。
declare @description nvarchar(max)=N'New description'
if exists (select 1 from fn_listextendedproperty(N'MS_Description',
N'SCHEMA', N'dbo',
N'TABLE',N'TableName', N'COLUMN',N'ColumnName'))
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@description,
@level0type=N'SCHEMA', @level0name=N'dbo',
@level1type=N'TABLE' , @level1name=N'TableName',
@level2type=N'COLUMN', @level2name=N'ColumnName'
ELSE
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@description ,
@level0type=N'SCHEMA', @level0name=N'dbo',
@level1type=N'TABLE' , @level1name=N'TableName',
@level2type=N'COLUMN', @level2name=N'ColumnName'
GO
- Mauricio Bolaños
0
我喜欢Brian Westrich和corky_bantam的答案。
以下是我尝试实现的两个人的结合。不确定是否有人会感兴趣，但我想分享一下。
我想能够为描述、源字段、源系统等设置多个不同的扩展属性。我实际上在顶部放置了另一个存储过程，然后可以将标准值提供给它。
我确实喜欢corky_bantam跨数据库运行的选项，但我决定在这个版本中保持简单。也许在将来，我会发现需要在数据库之间运行它，然后我会偷那个版本：）
我试图简化不存在的部分-争议是否真的更好，但我发现它更容易理解。
我使大多数值变量（我的高中IT老师会感到自豪），所以我可以为视图等运行此代码。
还要注意@column_name VARCHAR(500) = NULL，我这样做是因为在运行时根本不必提供变量。当在对象级别设置值时，我太懒了，不想一直输入@column_name = NULL。
CREATE OR ALTER PROCEDURE [tools].[sp_set_extended_properties]
@extended_property_name VARCHAR(500), --the extended property to set/update
@schema_name VARCHAR(500), --schema name
@object_name VARCHAR(500), --object name
@column_name VARCHAR(500) = NULL, -- column name, NULL if description for object
@value sql_variant --the value to assign
AS
BEGIN
SET NOCOUNT ON;
DECLARE @object_id INT
DECLARE @object_type VARCHAR(50)
DECLARE @column_id INT
SELECT
@object_id = o.object_id
,@object_type = CASE o.type
WHEN 'U' THEN 'TABLE'
ELSE o.type_desc END
,@column_id = c.column_id
FROM sys.objects o
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN sys.columns c on o.object_id = c.object_id
WHERE o.name = @object_name
AND s.name = @schema_name
AND c.name = ISNULL(@column_name, c.name)
--SELECT @object_id, @object_type, @column_id
IF @column_name IS NOT NULL
IF NOT EXISTS (SELECT NULL
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = @object_id
AND [name] = @extended_property_name
AND [minor_id] = @column_id)
EXECUTE sp_addextendedproperty @name = @extended_property_name, @value = @value,
@level0type = N'SCHEMA', @level0name = @schema_name, @level1type = @object_type,
@level1name = @object_name, @level2type = N'COLUMN', @level2name = @column_name;
ELSE
EXECUTE sp_updateextendedproperty @name = @extended_property_name,
@value = @value, @level0type = N'SCHEMA', @level0name = @schema_name,
@level1type = @object_type, @level1name = @object_name,
@level2type = N'COLUMN', @level2name = @column_name;
ELSE
IF NOT EXISTS (SELECT NULL
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = @object_id
AND [name] = @extended_property_name
AND [minor_id] = 0)
EXECUTE sp_addextendedproperty @name = @extended_property_name, @value = @value,
@level0type = N'SCHEMA', @level0name = @schema_name,
@level1type = @object_type, @level1name = @object_name;
ELSE
EXECUTE sp_updateextendedproperty @name = @extended_property_name, @value = @value,
@level0type = N'SCHEMA', @level0name = @schema_name,
@level1type = @object_type, @level1name = @object_name;
END
GO
- Ashley McLellan
0
在ScubaSteve的答案基础上，以下查询将允许您按名称检查指定模式中列或表上的MS_Description属性。要检查表，请将谓词c.name = '<column>'替换为c.name IS NULL或d.minor_id = 0
要按模式枚举MS_Description属性，请使用-
SELECT a.name as [schema], b.name as [table], c.name as [column], d.name, d.value
FROM sys.schemas a
JOIN sys.tables b ON a.schema_id = b.schema_id
LEFT JOIN sys.columns c ON b.object_id = c.object_id
JOIN sys.extended_properties d ON d.major_id = b.object_id AND d.minor_id = ISNULL(c.column_id,0)
WHERE a.name = '<schema>' AND b.name = '<table>' AND c.name = '<column>' AND d.name = 'MS_Description'
在添加 MS_Description 属性之前，可以通过将添加语句包含在以下代码中来检查其是否不存在 -
IF NOT EXISTS (SELECT 1 FROM sys.schemas a JOIN sys.tables b ON a.schema_id = b.schema_id LEFT JOIN sys.columns c ON b.object_id = c.object_id JOIN sys.extended_properties d ON d.major_id = b.object_id AND d.minor_id = ISNULL(c.column_id,0)
WHERE a.name = '<schema>' AND b.name = '<table>' AND c.name = '<column>' AND d.name = 'MS_Description'
)
BEGIN
--EXEC sp_addextendedproperty statement goes here
END
这些查询中的连接可能可以更好地排序以消除ISNULL函数，但这应该可以得到您想要的结果。
- Richard McDaniel
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接