主页 / dba / 问题 / 121712
Accepted
jmoreno
Asked: 2015-11-21 09:33:50 +0800 CST

对象属性列表

  • 772

是否有返回所有可用属性名称的函数或系统视图？

我正在寻找MSDN OBJECTPROPERTY 页面上未列出的“SYSTEMDATAACCESS”和“USERDATAACCESS” 。我添加了关于它们的评论，但我想知道是否还有其他我不知道的...鉴于它是 SQL，我希望它们可以在某处作为可查询的元数据使用，但找不到任何东西。元数据中的属性是否在某处？

编辑：虽然它与我的问题并不相关，但这是系统数据访问的示例用法。 select OBJECTPROPERTY(OBJECT_ID('sys.dm_db_stats_properties'), 'SYSTEMDATAACCESS')

sql-server

2 个回答

  1. Best Answer

    是否有返回所有可用属性名称的函数或系统视图？

    不，甚至没有东西可以返回所有可用内置函数的名称（例如它OBJECTPROPERTY自己）。

    这些属性值被记录在案，OBJECTPROPERTYEX即使它们似乎也适用于OBJECTPROPERTY并且可能应该在那里记录（返回类型是int所以没有理由不应该在那里支持它们，并且使用该函数更方便需要来自 sql_variant 的强制转换。）

    对于完全未记录的属性值（例如minlenfor INDEXPROPERTY），我不知道有任何方法可以确定这些值。

    • 2

  2. 这是一个脚本，允许您使用OBJECTPROPERTYEX函数https://msdn.microsoft.com/en-us/library/ms188390.aspx查询所有记录的属性名称和属性值

    OBJECTPROPERTYEX包含与 相同的所有属性OBJECTPROPERTY，但返回SQL_Variant类型而不是int，因此具有更多选项，例如BaseTypewhich 将返回 a char(2)。

    下面是 SQL 脚本，其中包含一些如何使用的示例：

      CREATE TABLE #tmpObjectPropertyEX_List (PropertyName nvarchar(50), PropertyAppliesTo nvarchar(256), PropertyDesc nvarchar(3000));
  INSERT INTO #tmpObjectPropertyEX_List (PropertyName, PropertyAppliesTo, PropertyDesc) 
   SELECT [PropertyName] = N'BaseType', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Identifies the base type of the object. When the specified object is a SYNONYM, the base type of the underlying object is returned. Nonnull = Object type Base data type: char(2)'     UNION ALL SELECT [PropertyName] = N'CnstIsClustKey', [PropertyAppliesToType] = N'Constraint', [PropertyDesc] = N'PRIMARY KEY constraint with a clustered index. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'CnstIsColumn', [PropertyAppliesToType] = N'Constraint', [PropertyDesc] = N'CHECK, DEFAULT, or FOREIGN KEY constraint on a single column. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'CnstIsDeleteCascade', [PropertyAppliesToType] = N'Constraint', [PropertyDesc] = N'FOREIGN KEY constraint with the ON DELETE CASCADE option. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'CnstIsDisabled', [PropertyAppliesToType] = N'Constraint', [PropertyDesc] = N'Disabled constraint. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'CnstIsNonclustKey', [PropertyAppliesToType] = N'Constraint', [PropertyDesc] = N'PRIMARY KEY constraint with a nonclustered index. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'CnstIsNotRepl', [PropertyAppliesToType] = N'Constraint', [PropertyDesc] = N'Constraint is defined by using the NOT FOR REPLICATION keywords. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'CnstIsNotTrusted', [PropertyAppliesToType] = N'Constraint', [PropertyDesc] = N'Constraint was enabled without checking existing rows. Therefore, the constraint may not hold for all rows. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'CnstIsUpdateCascade', [PropertyAppliesToType] = N'Constraint', [PropertyDesc] = N'FOREIGN KEY constraint with the ON UPDATE CASCADE option. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'ExecIsAfterTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'AFTER trigger. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'ExecIsAnsiNullsOn', [PropertyAppliesToType] = N'Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view', [PropertyDesc] = N'The setting of ANSI_NULLS at creation time. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'ExecIsDeleteTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'DELETE trigger. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'ExecIsFirstDeleteTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'The first trigger fired when a DELETE is executed against the table. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'ExecIsFirstInsertTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'The first trigger fired when an INSERT is executed against the table. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'ExecIsFirstUpdateTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'The first trigger fired when an UPDATE is executed against the table. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'ExecIsInsertTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'INSERT trigger. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'ExecIsInsteadOfTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'INSTEAD OF trigger. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'ExecIsLastDeleteTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'Last trigger fired when a DELETE is executed against the table. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'ExecIsLastInsertTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'Last trigger fired when an INSERT is executed against the table. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'ExecIsLastUpdateTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'Last trigger fired when an UPDATE is executed against the table. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'ExecIsQuotedIdentOn', [PropertyAppliesToType] = N'Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view', [PropertyDesc] = N'Setting of QUOTED_IDENTIFIER at creation time. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'ExecIsStartup', [PropertyAppliesToType] = N'Procedure', [PropertyDesc] = N'Startup procedure. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'ExecIsTriggerDisabled', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'Disabled trigger. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'ExecIsTriggerNotForRepl', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'Trigger defined as NOT FOR REPLICATION. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'ExecIsUpdateTrigger', [PropertyAppliesToType] = N'Trigger', [PropertyDesc] = N'UPDATE trigger. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'ExecIsWithNativeCompilation', [PropertyAppliesToType] = N'Transact-SQL Procedure', [PropertyDesc] = N'Procedure is natively compiled. 1 = True 0 = False Base data type: int Applies to: SQL Server 2014 through SQL Server 2016.'      UNION ALL SELECT [PropertyName] = N'HasAfterTrigger', [PropertyAppliesToType] = N'Table, view', [PropertyDesc] = N'Table or view has an AFTER trigger. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'HasDeleteTrigger', [PropertyAppliesToType] = N'Table, view', [PropertyDesc] = N'Table or view has a DELETE trigger. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'HasInsertTrigger', [PropertyAppliesToType] = N'Table, view', [PropertyDesc] = N'Table or view has an INSERT trigger. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'HasInsteadOfTrigger', [PropertyAppliesToType] = N'Table, view', [PropertyDesc] = N'Table or view has an INSTEAD OF trigger. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'HasUpdateTrigger', [PropertyAppliesToType] = N'Table, view', [PropertyDesc] = N'Table or view has an UPDATE trigger. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsAnsiNullsOn', [PropertyAppliesToType] = N'Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view', [PropertyDesc] = N'Specifies that the ANSI NULLS option setting for the table is ON, meaning all comparisons against a null value evaluate to UNKNOWN. This setting applies to all expressions in the table definition, including computed columns and constraints, for as long as the table exists. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'IsCheckCnst', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'CHECK constraint. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'IsConstraint', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Constraint. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsDefault', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Bound default. 1 = True 0 = False Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.'   UNION ALL SELECT [PropertyName] = N'IsDefaultCnst', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'DEFAULT constraint. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'IsDeterministic', [PropertyAppliesToType] = N'Scalar and table-valued functions, view', [PropertyDesc] = N'The determinism property of the function or view. 1 = Deterministic 0 = Not Deterministic Base data type: int'   UNION ALL SELECT [PropertyName] = N'IsEncrypted', [PropertyAppliesToType] = N'Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view', [PropertyDesc] = N'Indicates that the original text of the module statement was converted to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users without access to system tables or database files cannot retrieve the obfuscated text. However, the text is available to users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at run time. 1 = Encrypted 0 = Not encrypted Base data type: int'     UNION ALL SELECT [PropertyName] = N'IsExecuted', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Specifies the object can be executed (view, procedure, function, or trigger). 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsExtendedProc', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Extended procedure. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'IsForeignKey', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'FOREIGN KEY constraint. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsIndexed', [PropertyAppliesToType] = N'Table, view', [PropertyDesc] = N'A table or view with an index. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'IsIndexable', [PropertyAppliesToType] = N'Table, view', [PropertyDesc] = N'A table or view on which an index may be created. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsInlineFunction', [PropertyAppliesToType] = N'Function', [PropertyDesc] = N'Inline function. 1 = Inline function 0 = Not inline function Base data type: int'      UNION ALL SELECT [PropertyName] = N'IsMSShipped', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'An object created during installation of SQL Server. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsPrecise', [PropertyAppliesToType] = N'Computed column, function, user-defined type, view', [PropertyDesc] = N'Indicates whether the object contains an imprecise computation, such as floating point operations. 1 = Precise 0 = Imprecise Base data type: int'   UNION ALL SELECT [PropertyName] = N'IsPrimaryKey', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'PRIMARY KEY constraint. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsProcedure', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Procedure. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'IsQuotedIdentOn', [PropertyAppliesToType] = N'CHECK constraint, DEFAULT definition, Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view', [PropertyDesc] = N'Specifies that the quoted identifier setting for the object is ON, meaning double quotation marks delimit identifiers in all expressions involved in the object definition. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'IsQueue', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Service Broker Queue 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsReplProc', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Replication procedure. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'IsRule', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Bound rule. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'IsScalarFunction', [PropertyAppliesToType] = N'Function', [PropertyDesc] = N'Scalar-valued function. 1 = Scalar-valued function 0 = Not scalar-valued function Base data type: int'     UNION ALL SELECT [PropertyName] = N'IsSchemaBound', [PropertyAppliesToType] = N'Function, Procedure, view', [PropertyDesc] = N'A schema bound function or view created by using SCHEMABINDING. 1 = Schema-bound 0 = Not schema-bound Base data type: int'   UNION ALL SELECT [PropertyName] = N'IsSystemTable', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'System table. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsSystemVerified', [PropertyAppliesToType] = N'Computed column, function, user-defined type, view', [PropertyDesc] = N'The precision and determinism properties of the object can be verified by SQL Server. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsTable', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'IsTableFunction', [PropertyAppliesToType] = N'Function', [PropertyDesc] = N'Table-valued function. 1 = Table-valued function 0 = Not table-valued function Base data type: int'     UNION ALL SELECT [PropertyName] = N'IsTrigger', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Trigger. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'IsUniqueCnst', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'UNIQUE constraint. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'IsUserTable', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'User-defined table. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'IsView', [PropertyAppliesToType] = N'View', [PropertyDesc] = N'View. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'OwnerId', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'Owner of the object. System_CAPS_noteNote The schema owner is not necessarily the object owner. For example, child objects (those where parent_object_id is nonnull) will always return the same owner ID as the parent. Nonnull = Database user ID of the object owner. NULL = Unsupported object type, or object ID is not valid. Base data type: int'    UNION ALL SELECT [PropertyName] = N'SchemaId', [PropertyAppliesToType] = N'Any schema-scoped object', [PropertyDesc] = N'The ID of the schema associated with the object. Nonnull = Schema ID of the object. Base data type: int'   UNION ALL SELECT [PropertyName] = N'SystemDataAccess', [PropertyAppliesToType] = N'Function, view', [PropertyDesc] = N'Object accesses system data, system catalogs or virtual system tables, in the local instance of SQL Server. 0 = None 1 = Read Base data type: int'   UNION ALL SELECT [PropertyName] = N'TableDeleteTrigger', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a DELETE trigger. >1 = ID of first trigger with the specified type. Base data type: int'      UNION ALL SELECT [PropertyName] = N'TableDeleteTriggerCount', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'The table has the specified number of DELETE triggers. Nonnull = Number of DELETE triggers Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableFullTextMergeStatus', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Whether a table that has a full-text index that is currently in merging. 0 = Table does not have a full-text index, or the full-text index is not in merging. 1 = The full-text index is in merging. Applies to: SQL Server 2008 through SQL Server 2016.'    UNION ALL SELECT [PropertyName] = N'TableFullTextBackgroundUpdateIndexOn', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'The table has full-text background update index (autochange tracking) enabled. 1 = TRUE 0 = FALSE Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.'   UNION ALL SELECT [PropertyName] = N'TableFulltextCatalogId', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'ID of the full-text catalog in which the full-text index data for the table resides. Nonzero = Full-text catalog ID, associated with the unique index that identifies the rows in a full-text indexed table. 0 = Table does not have a full-text index. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.'   UNION ALL SELECT [PropertyName] = N'TableFullTextChangeTrackingOn', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has full-text change-tracking enabled. 1 = TRUE 0 = FALSE Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.'    UNION ALL SELECT [PropertyName] = N'TableFulltextDocsProcessed', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Number of rows processed since the start of full-text indexing. In a table that is being indexed for full-text search, all the columns of one row are considered as part of one document to be indexed. 0 = No active crawl or full-text indexing is completed. > 0 = One of the following:  The number of documents processed by insert or update operations since the start of full, incremental, or manual change tracking population. The number of rows processed by insert or update operations since change tracking with background update index population was enabled, the full-text index schema changed, the full-text catalog rebuilt, or the instance of SQL Server restarted, and so on.  NULL = Table does not have a full-text index. Base data type: int Note   This property does not monitor or count deleted rows. Applies to: SQL Server 2008 through SQL Server 2016.'   UNION ALL SELECT [PropertyName] = N'TableFulltextFailCount', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'The number of rows that full-text search did not index. 0 = The population has completed. >0 = One of the following:  The number of documents that were not indexed since the start of Full, Incremental, and Manual Update change tracking population. For change tracking with background update index, the number of rows that were not indexed since the start of the population, or the restart of the population. This could be caused by a schema change, rebuild of the catalog, server restart, and so on  NULL = Table does not have a Full-Text index. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.'     UNION ALL SELECT [PropertyName] = N'TableFulltextItemCount', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Nonnull = Number of rows that were full-text indexed successfully. NULL = Table does not have a full-text index. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.'      UNION ALL SELECT [PropertyName] = N'TableFulltextKeyColumn', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'ID of the column associated with the single-column unique index that is part of the definition of a full-text index and semantic index. 0 = Table does not have a full-text index. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.'    UNION ALL SELECT [PropertyName] = N'TableFulltextPendingChanges', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Number of pending change tracking entries to process. 0 = change tracking is not enabled. NULL = Table does not have a full-text index. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.'      UNION ALL SELECT [PropertyName] = N'TableFulltextPopulateStatus', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'0 = Idle. 1 = Full population is in progress. 2 = Incremental population is in progress. 3 = Propagation of tracked changes is in progress. 4 = Background update index is in progress, such as autochange tracking. 5 = Full-text indexing is throttled or paused. 6 = An error has occurred. Examine the crawl log for details. For more information, see the Troubleshooting Errors in a Full-Text Population (Crawl) section of Populate Full-Text Indexes. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.'      UNION ALL SELECT [PropertyName] = N'TableFullTextSemanticExtraction', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table is enabled for semantic indexing. 1 = True 0 = False Base data type: int Applies to: SQL Server 2012 through SQL Server 2016.'   UNION ALL SELECT [PropertyName] = N'TableHasActiveFulltextIndex', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has an active full-text index. 1 = True 0 = False Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.'      UNION ALL SELECT [PropertyName] = N'TableHasCheckCnst', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a CHECK constraint. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableHasClustIndex', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a clustered index. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableHasDefaultCnst', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a DEFAULT constraint. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableHasDeleteTrigger', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a DELETE trigger. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'TableHasForeignKey', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a FOREIGN KEY constraint. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'TableHasForeignRef', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table is referenced by a FOREIGN KEY constraint. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableHasIdentity', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has an identity column. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'TableHasIndex', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has an index of any type. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'TableHasInsertTrigger', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Object has an INSERT trigger. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableHasNonclustIndex', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'The table has a nonclustered index. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'TableHasPrimaryKey', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a primary key. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableHasRowGuidCol', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a ROWGUIDCOL for a uniqueidentifier column. 1 = True 0 = False Base data type: int'   UNION ALL SELECT [PropertyName] = N'TableHasTextImage', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a text, ntext, or image column. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableHasTimestamp', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a timestamp column. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableHasUniqueCnst', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a UNIQUE constraint. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'TableHasUpdateTrigger', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'The object has an UPDATE trigger. 1 = True 0 = False Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableHasVarDecimalStorageFormat', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table is enabled for vardecimal storage format. 1 = True 0 = False'    UNION ALL SELECT [PropertyName] = N'TableInsertTrigger', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has an INSERT trigger. >1 = ID of first trigger with the specified type. Base data type: int'     UNION ALL SELECT [PropertyName] = N'TableInsertTriggerCount', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'The table has the specified number of INSERT triggers. >0 = The number of INSERT triggers. Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableIsFake', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table is not real. It is materialized internally on demand by the Database Engine. 1 = True 0 = False Base data type: int'     UNION ALL SELECT [PropertyName] = N'TableIsLockedOnBulkLoad', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table is locked because a bcp or BULK INSERT job. 1 = True 0 = False Base data type: int'      UNION ALL SELECT [PropertyName] = N'TableIsMemoryOptimized', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table is memory optimized 1 = True 0 = False Base data type: int For more information, see In-Memory OLTP (In-Memory Optimization). Applies to: SQL Server 2014 through SQL Server 2016.'   UNION ALL SELECT [PropertyName] = N'TableIsPinned', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table is pinned to be held in the data cache. 0 = False This feature is not supported in SQL Server 2005 and later versions.'    UNION ALL SELECT [PropertyName] = N'TableTextInRowLimit', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has text in row option set. > 0 = Maximum bytes allowed for text in row. 0 = text in row option is not set. Base data type: int'     UNION ALL SELECT [PropertyName] = N'TableUpdateTrigger', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has an UPDATE trigger. > 1 = ID of first trigger with the specified type. Base data type: int'    UNION ALL SELECT [PropertyName] = N'TableUpdateTriggerCount', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has the specified number of UPDATE triggers. > 0 = The number of UPDATE triggers. Base data type: int'   UNION ALL SELECT [PropertyName] = N'UserDataAccess', [PropertyAppliesToType] = N'Function, View', [PropertyDesc] = N'Indicates the object accesses user data, user tables, in the local instance of SQL Server. 1 = Read 0 = None Base data type: int'      UNION ALL SELECT [PropertyName] = N'TableHasColumnSet', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Table has a column set. 0 = False 1 = True For more information, see Use Column Sets.'   UNION ALL SELECT [PropertyName] = N'Cardinality', [PropertyAppliesToType] = N'Table (system or user-defined), view, or index', [PropertyDesc] = N'The number of rows in the specified object. Applies to: SQL Server 2012 through SQL Server 2016.'     UNION ALL SELECT [PropertyName] = N'TableTemporalType', [PropertyAppliesToType] = N'Table', [PropertyDesc] = N'Specifies the type of table. 0 = non-temporal table 1 = history table for system-versioned table 2 = system-versioned temporal table Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.'     
  ;

  --List all available object properties: 
  SELECT * FROM #tmpObjectPropertyEX_List ORDER BY 1, 2;

  --List all object properties for a given object:
  SELECT 
     objectName = isnull(SCHEMA_NAME(o.schema_id) + '.', '') + o.name
    , op.PropertyName
    , op.PropertyAppliesTo
    , PropertyValue = OBJECTPROPERTYEX(o.object_id, op.PropertyName)
    , o.*
  FROM sys.objects o 
    cross apply #tmpObjectPropertyEX_List op
  WHERE o.object_id = object_id('dbo.MyObjectName')
    and OBJECTPROPERTYEX(o.object_id, op.PropertyName) is not null
  ;

    要重新创建值列表：

    1. 将上面 MSDN URL 中的 HTML 表复制粘贴到 Excel 中，并在粘贴时选择“匹配目标格式”，以便不复制 colspan。这将为每个属性提供多行，额外的行以 2 个制表符为前缀。
    2. 将 Excel 中的所有结果复制到 Notepad++（如果您直接复制到 Notepad++，它不包括每个属性的附加行上的 2 个前缀选项卡）。
    3. 查找/替换：“\r\n\t\t”与“”（两个空格）。这会将所有内容移动到每个属性一行，包含 3 列
    4. 用两个单引号 "''" ...或 "'" 或不破坏 SQL 语句的内容查找/替换任何单引号 "'"

    5. 粘贴回 Excel，然后在每行的单元格 D 中添加此公式以构建 Select SQL 语句：
      =IF(ROW(A1)=1,"","UNION ALL ") & " SELECT [PropertyName] = N' "&A1&"'，[PropertyAppliesToType] = N'"&B1&"'，[PropertyDesc] = N'"&C1&"'"

    6. 将 D 列中的 Excel 公式结果复制/粘贴回 Notepad++，然后用“\t”查找/替换：“\r\n”，以便将所有内容放在一行中。

    7. 将“SELECT”语句复制/粘贴回下面的插入语句
    • 2

相关问题