Created
September 27, 2023 06:09
-
-
Save martin-guth/1a8fc51e43ea083a79e1a1839a396836 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "metadata": { | |
| "kernelspec": { | |
| "name": "SQL", | |
| "display_name": "SQL", | |
| "language": "sql" | |
| }, | |
| "language_info": { | |
| "name": "sql", | |
| "version": "" | |
| }, | |
| "extensions": { | |
| "azuredatastudio": { | |
| "version": 1, | |
| "views": [] | |
| } | |
| } | |
| }, | |
| "nbformat_minor": 2, | |
| "nbformat": 4, | |
| "cells": [ | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "# Demo of Helper Procedure pr\\_setDefaultExtendedProperties\n", | |
| "\n", | |
| "This procedure sets default extended properties on undocumented columns of a table or the table itself. It is written so you can run it multiple times....if the property is already present it leaves it alone if not...it is created. \n", | |
| "\n", | |
| "My default properties are:\n", | |
| "\n", | |
| "- Author (supplied via parameter @paramExtendedPropertyAuthor)\n", | |
| "- ChangeDate\n", | |
| "- ChangeHistory\n", | |
| "- CreationDate (defaults to GETDATE in German Format dd.MM.yyyy)\n", | |
| "- MS\\_Description\n", | |
| "\n", | |
| "### Prerequisites \n", | |
| "\n", | |
| "Grab the source code [here](https://github.com/martin-guth/DataDictionaryCreator/blob/main/HelperProcedures/pr_setDefaultExtendedProperties.sql) <span style=\"font-size: 14px;\"> and run it on your database</span><span style=\"font-size: 14px;\">.The procedure is supposed to be created in the same database where the objects live you would like to document. If you have 10 databases you would need 1 procedure per each database.</span>\n", | |
| "\n", | |
| "Here are a few procedure calls demonstrating the behaviour.\n", | |
| "\n", | |
| "First we create a test table in the database of your choice." | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "ee07486d-c749-4d2a-8d33-8973aec78725" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "DROP TABLE IF EXISTS dbo.test;\r\n", | |
| "GO\r\n", | |
| "CREATE TABLE dbo.test \r\n", | |
| "(\r\n", | |
| "\ta INT NOT NULL IDENTITY(1,1),\r\n", | |
| "\tb VARCHAR(20) NULL,\r\n", | |
| "\tCONSTRAINT test_pk PRIMARY KEY CLUSTERED (a)\r\n", | |
| ");" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "e555ed2e-4579-4223-a24b-d249ed3b07ae" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Commands completed successfully." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Commands completed successfully." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.057" | |
| }, | |
| "metadata": {} | |
| } | |
| ], | |
| "execution_count": 4 | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "Let's double check that we don't have any extended properties present yet. The following SQL should return 0 rows." | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "af05b133-364b-4e42-8893-f879a18dcc39" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "SELECT \r\n", | |
| " ob.name AS objectName,\r\n", | |
| " '0Table' AS columnName,\r\n", | |
| " ep.name AS propertyName,\r\n", | |
| " ep.value AS propertyValue\r\n", | |
| "FROM \r\n", | |
| " sys.objects ob\r\n", | |
| "INNER JOIN sys.extended_properties ep ON ep.major_id = ob.object_id AND ep.minor_id = 0\r\n", | |
| "WHERE\r\n", | |
| " ob.name = 'test'\r\n", | |
| "AND ob.type_desc IN ('VIEW', 'USER_TABLE')\r\n", | |
| "AND ep.class_desc = 'OBJECT_OR_COLUMN'\r\n", | |
| "UNION ALL\r\n", | |
| "SELECT \r\n", | |
| " ob.name AS objectName,\r\n", | |
| " c.name AS columnName,\r\n", | |
| " ep.name AS propertyName,\r\n", | |
| " ep.value AS propertyValue\r\n", | |
| "FROM \r\n", | |
| " sys.objects ob\r\n", | |
| "INNER JOIN sys.columns c ON c.object_id = ob.object_id \r\n", | |
| "INNER JOIN sys.extended_properties ep ON ep.major_id = ob.object_id AND ep.minor_id = c.column_id\r\n", | |
| "WHERE\r\n", | |
| " ob.name = 'test'\r\n", | |
| "AND ob.type_desc IN ('VIEW', 'USER_TABLE')\r\n", | |
| "AND ep.class_desc = 'OBJECT_OR_COLUMN'\r\n", | |
| "ORDER BY\r\n", | |
| " objectName,\r\n", | |
| " columnName\r\n", | |
| ";\r\n", | |
| "" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "044cadd8-57cc-42b2-9990-c1987cd322ff" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(0 rows affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.070" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "metadata": {}, | |
| "execution_count": 5, | |
| "data": { | |
| "application/vnd.dataresource+json": { | |
| "schema": { | |
| "fields": [ | |
| { | |
| "name": "objectName" | |
| }, | |
| { | |
| "name": "columnName" | |
| }, | |
| { | |
| "name": "propertyName" | |
| }, | |
| { | |
| "name": "propertyValue" | |
| } | |
| ] | |
| }, | |
| "data": [] | |
| }, | |
| "text/html": [ | |
| "<table>", | |
| "<tr><th>objectName</th><th>columnName</th><th>propertyName</th><th>propertyValue</th></tr>", | |
| "</table>" | |
| ] | |
| } | |
| } | |
| ], | |
| "execution_count": 5 | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "Now execute the procedure. \n", | |
| "Observe the output...the object as well as the column names are listed." | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "cdf65be1-9fc6-4e17-8863-1a540a35b177" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "EXECUTE dbo.pr_setDefaultExtendedProperty \r\n", | |
| "\t@paramSchemaNameDestination = 'dbo',\r\n", | |
| "\t@paramObjectNameDestination = 'test',\r\n", | |
| "\t@paramObjectTypeDestination = 'U', /* user table...if you omit this parameter you get a list of allowed values returned */\r\n", | |
| "\t@paramExtendedPropertyAuthor = 'Mickey Mouse'\r\n", | |
| ";" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "4aebd483-3824-4140-afb6-54e5e4acbb87" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(5 rows affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(2 rows affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Schema: dbo" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Objekt: test" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Typ: TABLE" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": " " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "========================================================================================================================" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Schema: dbo" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Objekt: test" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Typ: TABLE" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Spalte: a" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": " " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'Author' Neuer Wert 'Mickey Mouse'" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeDate' Neuer Wert ''" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeHistory' Neuer Wert ''" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'CreationDate' Neuer Wert '27.09.2023'" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'MS_Description' Neuer Wert ''" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "========================================================================================================================" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Schema: dbo" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Objekt: test" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Typ: TABLE" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Spalte: b" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": " " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'Author' Neuer Wert 'Mickey Mouse'" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeDate' Neuer Wert ''" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeHistory' Neuer Wert ''" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'CreationDate' Neuer Wert '27.09.2023'" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'MS_Description' Neuer Wert ''" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "========================================================================================================================" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.237" | |
| }, | |
| "metadata": {} | |
| } | |
| ], | |
| "execution_count": 6 | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "Check Extended Properties again....now with some PIVOTing magic --\\> Extended Properties are present" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "469e8155-3abc-48b8-a618-5cf638bde628" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "SELECT \n", | |
| " objectName,\n", | |
| " columnName,\n", | |
| " [MS_Description],\n", | |
| " [Author],\n", | |
| " [CreationDate],\n", | |
| " [ChangeDate],\n", | |
| " [ChangeHistory]\n", | |
| "FROM \n", | |
| "(\n", | |
| " SELECT \n", | |
| " ob.name AS objectName,\n", | |
| " '0Table' AS columnName,\n", | |
| " ep.name AS propertyName,\n", | |
| " ep.value AS propertyValue\n", | |
| " FROM \n", | |
| " sys.objects ob\n", | |
| " LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = ob.object_id AND ep.minor_id = 0\n", | |
| " WHERE\n", | |
| " ob.name = 'test'\n", | |
| " AND ob.type_desc IN ('VIEW', 'USER_TABLE')\n", | |
| " AND ep.class_desc = 'OBJECT_OR_COLUMN'\n", | |
| " UNION ALL\n", | |
| " SELECT \n", | |
| " ob.name AS objectName,\n", | |
| " c.name AS columnName,\n", | |
| " ep.name AS propertyName,\n", | |
| " ep.value AS propertyValue\n", | |
| " FROM \n", | |
| " sys.objects ob\n", | |
| " LEFT OUTER JOIN sys.columns c ON c.object_id = ob.object_id \n", | |
| " LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = ob.object_id AND ep.minor_id = c.column_id\n", | |
| " WHERE\n", | |
| " ob.name = 'test'\n", | |
| " \n", | |
| " AND ob.type_desc IN ('VIEW', 'USER_TABLE')\n", | |
| " AND ep.class_desc = 'OBJECT_OR_COLUMN'\n", | |
| ") src\n", | |
| "PIVOT (MAX(propertyValue) FOR propertyName IN ([MS_Description], [Author], [CreationDate], [ChangeDate], [ChangeHistory])\n", | |
| ") piv" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "c7c29bb5-33e6-49be-ab31-769a26628a77" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(3 rows affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.010" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "metadata": {}, | |
| "execution_count": 7, | |
| "data": { | |
| "application/vnd.dataresource+json": { | |
| "schema": { | |
| "fields": [ | |
| { | |
| "name": "objectName" | |
| }, | |
| { | |
| "name": "columnName" | |
| }, | |
| { | |
| "name": "MS_Description" | |
| }, | |
| { | |
| "name": "Author" | |
| }, | |
| { | |
| "name": "CreationDate" | |
| }, | |
| { | |
| "name": "ChangeDate" | |
| }, | |
| { | |
| "name": "ChangeHistory" | |
| } | |
| ] | |
| }, | |
| "data": [ | |
| { | |
| "0": "test", | |
| "1": "0Table", | |
| "2": "", | |
| "3": "Mickey Mouse", | |
| "4": "27.09.2023", | |
| "5": "", | |
| "6": "" | |
| }, | |
| { | |
| "0": "test", | |
| "1": "a", | |
| "2": "", | |
| "3": "Mickey Mouse", | |
| "4": "27.09.2023", | |
| "5": "", | |
| "6": "" | |
| }, | |
| { | |
| "0": "test", | |
| "1": "b", | |
| "2": "", | |
| "3": "Mickey Mouse", | |
| "4": "27.09.2023", | |
| "5": "", | |
| "6": "" | |
| } | |
| ] | |
| }, | |
| "text/html": [ | |
| "<table>", | |
| "<tr><th>objectName</th><th>columnName</th><th>MS_Description</th><th>Author</th><th>CreationDate</th><th>ChangeDate</th><th>ChangeHistory</th></tr>", | |
| "<tr><td>test</td><td>0Table</td><td></td><td>Mickey Mouse</td><td>27.09.2023</td><td></td><td></td></tr>", | |
| "<tr><td>test</td><td>a</td><td></td><td>Mickey Mouse</td><td>27.09.2023</td><td></td><td></td></tr>", | |
| "<tr><td>test</td><td>b</td><td></td><td>Mickey Mouse</td><td>27.09.2023</td><td></td><td></td></tr>", | |
| "</table>" | |
| ] | |
| } | |
| } | |
| ], | |
| "execution_count": 7 | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "Now let's add a new column...execute the procedure again and see what happens" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "6e268084-6655-4c29-ba02-947fdca709f4" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "ALTER TABLE dbo.test ADD c NUMERIC(10,2);\r\n", | |
| "\r\n", | |
| "EXECUTE dbo.pr_setDefaultExtendedProperty \r\n", | |
| "\t@paramSchemaNameDestination = 'dbo',\r\n", | |
| "\t@paramObjectNameDestination = 'test',\r\n", | |
| "\t@paramObjectTypeDestination = 'U', /* user table...if you omit this parameter you get a list of allowed values returned */\r\n", | |
| "\t@paramExtendedPropertyAuthor = 'Donald Duck'\r\n", | |
| ";" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "92fcbe10-039c-44ed-8831-36e390f013da" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(5 rows affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(3 rows affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Schema: dbo" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Objekt: test" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Typ: TABLE" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": " " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'Author' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeDate' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeHistory' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'CreationDate' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'MS_Description' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "========================================================================================================================" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Schema: dbo" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Objekt: test" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Typ: TABLE" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Spalte: a" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": " " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'Author' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeDate' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeHistory' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'CreationDate' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'MS_Description' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "========================================================================================================================" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Schema: dbo" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Objekt: test" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Typ: TABLE" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Spalte: b" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": " " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'Author' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeDate' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeHistory' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'CreationDate' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'MS_Description' ist bereits gesetzt. Es wurde keine Aktion durchgeführt." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "========================================================================================================================" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Schema: dbo" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Objekt: test" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Typ: TABLE" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Spalte: c" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": " " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'Author' Neuer Wert 'Donald Duck'" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeDate' Neuer Wert ''" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'ChangeHistory' Neuer Wert ''" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'CreationDate' Neuer Wert '27.09.2023'" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Extended Property 'MS_Description' Neuer Wert ''" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "========================================================================================================================" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.092" | |
| }, | |
| "metadata": {} | |
| } | |
| ], | |
| "execution_count": 8 | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "Observe the output: Only for the new column c the values are printed out. The output claims that no action was done for the other columns and the table level.\n", | |
| "\n", | |
| "Let's verify this by checking the extended properties again." | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "0daf3ed8-ff70-4525-bdb1-50ac4de7746b" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "SELECT \r\n", | |
| " objectName,\r\n", | |
| " columnName,\r\n", | |
| " [MS_Description],\r\n", | |
| " [Author],\r\n", | |
| " [CreationDate],\r\n", | |
| " [ChangeDate],\r\n", | |
| " [ChangeHistory]\r\n", | |
| "FROM \r\n", | |
| "(\r\n", | |
| " SELECT \r\n", | |
| " ob.name AS objectName,\r\n", | |
| " '0Table' AS columnName,\r\n", | |
| " ep.name AS propertyName,\r\n", | |
| " ep.value AS propertyValue\r\n", | |
| " FROM \r\n", | |
| " sys.objects ob\r\n", | |
| " LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = ob.object_id AND ep.minor_id = 0\r\n", | |
| " WHERE\r\n", | |
| " ob.name = 'test'\r\n", | |
| " AND ob.type_desc IN ('VIEW', 'USER_TABLE')\r\n", | |
| " AND ep.class_desc = 'OBJECT_OR_COLUMN'\r\n", | |
| " UNION ALL\r\n", | |
| " SELECT \r\n", | |
| " ob.name AS objectName,\r\n", | |
| " c.name AS columnName,\r\n", | |
| " ep.name AS propertyName,\r\n", | |
| " ep.value AS propertyValue\r\n", | |
| " FROM \r\n", | |
| " sys.objects ob\r\n", | |
| " LEFT OUTER JOIN sys.columns c ON c.object_id = ob.object_id \r\n", | |
| " LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = ob.object_id AND ep.minor_id = c.column_id\r\n", | |
| " WHERE\r\n", | |
| " ob.name = 'test'\r\n", | |
| " \r\n", | |
| " AND ob.type_desc IN ('VIEW', 'USER_TABLE')\r\n", | |
| " AND ep.class_desc = 'OBJECT_OR_COLUMN'\r\n", | |
| ") src\r\n", | |
| "PIVOT (MAX(propertyValue) FOR propertyName IN ([MS_Description], [Author], [CreationDate], [ChangeDate], [ChangeHistory])\r\n", | |
| ") piv" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "e47fcd14-2c39-4f78-adc5-724f31e2f18b" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(4 rows affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.009" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "metadata": {}, | |
| "execution_count": 9, | |
| "data": { | |
| "application/vnd.dataresource+json": { | |
| "schema": { | |
| "fields": [ | |
| { | |
| "name": "objectName" | |
| }, | |
| { | |
| "name": "columnName" | |
| }, | |
| { | |
| "name": "MS_Description" | |
| }, | |
| { | |
| "name": "Author" | |
| }, | |
| { | |
| "name": "CreationDate" | |
| }, | |
| { | |
| "name": "ChangeDate" | |
| }, | |
| { | |
| "name": "ChangeHistory" | |
| } | |
| ] | |
| }, | |
| "data": [ | |
| { | |
| "0": "test", | |
| "1": "0Table", | |
| "2": "", | |
| "3": "Mickey Mouse", | |
| "4": "27.09.2023", | |
| "5": "", | |
| "6": "" | |
| }, | |
| { | |
| "0": "test", | |
| "1": "a", | |
| "2": "", | |
| "3": "Mickey Mouse", | |
| "4": "27.09.2023", | |
| "5": "", | |
| "6": "" | |
| }, | |
| { | |
| "0": "test", | |
| "1": "b", | |
| "2": "", | |
| "3": "Mickey Mouse", | |
| "4": "27.09.2023", | |
| "5": "", | |
| "6": "" | |
| }, | |
| { | |
| "0": "test", | |
| "1": "c", | |
| "2": "", | |
| "3": "Donald Duck", | |
| "4": "27.09.2023", | |
| "5": "", | |
| "6": "" | |
| } | |
| ] | |
| }, | |
| "text/html": [ | |
| "<table>", | |
| "<tr><th>objectName</th><th>columnName</th><th>MS_Description</th><th>Author</th><th>CreationDate</th><th>ChangeDate</th><th>ChangeHistory</th></tr>", | |
| "<tr><td>test</td><td>0Table</td><td></td><td>Mickey Mouse</td><td>27.09.2023</td><td></td><td></td></tr>", | |
| "<tr><td>test</td><td>a</td><td></td><td>Mickey Mouse</td><td>27.09.2023</td><td></td><td></td></tr>", | |
| "<tr><td>test</td><td>b</td><td></td><td>Mickey Mouse</td><td>27.09.2023</td><td></td><td></td></tr>", | |
| "<tr><td>test</td><td>c</td><td></td><td>Donald Duck</td><td>27.09.2023</td><td></td><td></td></tr>", | |
| "</table>" | |
| ] | |
| } | |
| } | |
| ], | |
| "execution_count": 9 | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "Author Donald Duck has only been set for the new column c as expected.\n", | |
| "\n", | |
| "You have trouble putting the right object type?..we got you covered...if you leave it out you get an error message listing the valid values:" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "ab1644ca-2e6e-4e23-b91c-ded58345a74b" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "\r\n", | |
| "EXECUTE dbo.pr_setDefaultExtendedProperty \r\n", | |
| "\t@paramSchemaNameDestination = 'dbo',\r\n", | |
| "\t@paramObjectNameDestination = 'test',\r\n", | |
| "\t@paramObjectTypeDestination = NULL,\r\n", | |
| "\t@paramExtendedPropertyAuthor = 'Donald Duck'\r\n", | |
| ";" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "8b9b3666-a313-4237-bc74-3c7d3be4ac28" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Parameter @paramObjectTypeDestination hat eine falsche Eingabe." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Unterstützte Eingaben sind: " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "U für User Table, " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "V für View, " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "P für SQL_STORED_PROCEDURE, " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "FN für SQL_SCALAR_FUNCTION, " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "TF für SQL_TABLE_VALUED_FUNCTION, " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "IN für SQL_INLINE_TABLE_VALUED_FUNCTION, " | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "TR für TRIGGER angeben." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "TT für Tabellentyp angeben." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.036" | |
| }, | |
| "metadata": {} | |
| } | |
| ], | |
| "execution_count": 12 | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "/* finally let's cleanup the demo table */\r\n", | |
| "IF OBJECT_ID('dbo.test') IS NOT NULL\r\n", | |
| " DROP TABLE dbo.test;" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "44ab0817-9d7d-45f5-a0b1-7db258441c76" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Commands completed successfully." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.003" | |
| }, | |
| "metadata": {} | |
| } | |
| ], | |
| "execution_count": 14 | |
| } | |
| ] | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment