![]() ![]() VARCHAR ( 100 ), VARCHAR ( 100 ) AS DECLARE DATETIME SET = ( SELECT GETDATE ( ) ) DECLARE SQL NVARCHAR ( MAX ) DECLARE NVARCHAR ( MAX ) DECLARE VARCHAR ( 50 ) SET = ( SELECT REPLACE (, ' ', '' ) ) /******************This section drops and recreates the output table*******************/ SET = ' DROP TABLE IF EXISTS. The following example lists extended properties for all tables contained in the Sales schema.CREATE PROC. Displaying extended properties on all tables in a schema USE AdventureWorks2012 įROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'ScrapReason', 'column', default) ĬOLUMN ScrapReasonID MS_Description Primary key for ScrapReason records.ĬOLUMN Name MS_Description Failure description.ĬOLUMN ModifiedDate MS_Description Date the record was last updated. This is contained in the schema Production. The following example lists extended properties for columns in the ScrapReason table. Displaying extended properties on all columns in a table ![]() NULL NULL MS_Description AdventureWorks2008 Sample OLTP Database USE AdventureWorks2012 įROM fn_listextendedproperty(default, default, default, default, default, default, default) ![]() The following example displays all extended properties set on the database object itself. Displaying extended properties on a database Permissions to list extended properties of objects vary by object type. ON o.name = e.objname COLLATE SQL_Latin1_General_CP1_CI_AS LEFT JOIN sys.fn_listextendedproperty(N'MS_Description', N'user',N'HumanResources',N'table', N'Employee', null, default) AS e NULL AS 'column_length', Cast(e.value AS varchar(500)) AS 'column_description' 'table_name',Ġ AS 'column_order', NULL AS 'column_name', NULL AS 'column_datatype', However you can workaround this by overriding collation in comparison. Objname is fixed as Latin1_General_CI_AI. Otherwise, the function returns an empty result set. If a lower-level object, level 1 or 2, type and name are specified, the parent object type and name should be given values that are not NULL or default. The objects are distinguished according to levels, with level 0 as the highest and level 2 the lowest. When the object type is specified and the value of the corresponding object name is NULL or default, fn_listextendedproperty returns all extended properties for all objects of the type specified. If the value for property_name is NULL or default, fn_listextendedproperty returns all the properties for the specified object. When returning extended properties on the database itself, the objtype and objname columns will be NULL. If the table returned is empty, either the object does not have extended properties or the user does not have permissions to list the extended properties on the object. This is the format of the tables returned by fn_listextendedproperty. Valid inputs are default, NULL, or an object name. level2_object_name is sysname with a default of NULL. Is the name of the level 2 object type specified. Returns extended property values of database objects. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |