💘 👍🏼 ➿ Chargement de FIAS dans la base de données sur MSSQLSERVER par des moyens improvisés (SQLXMLBULKLOAD). Comment cela (probablement) n'a pas besoin d'être fait 👩🏼‍💼 🎖️ 🙄

Épigraphe:
"Quand vous avez un marteau dans les mains, tout autour de vous semble être des clous."

Il y a longtemps, semble-t-il - vendredi dernier, en faisant le tour du quartier du bureau, j'ai pris soin des maudits patrons que je passe du temps dans l'oisiveté et la contemplation des chats et des chatons.

"Mais voudriez-vous télécharger FIAS, un ami du siège!" - ont déclaré les autorités. - Pour le processus de téléchargement quelque chose n'aime pas nos unités commerciales. Pendant longtemps, ils disent que ça se charge, le serveur de nourriture se charge et le mec qui a écrit le processus de démarrage a quitté l'autre jour, trois ans déjà.
De plus, tout ce qui doit être refait depuis longtemps, alors vous le prenez, créez une base pour vous-même et assurez le remplissage périodique de FIAS. Tous, comme on dit, je ne tarde pas!

Ici, je dois dire que j'ai une relation éloignée avec la programmation, car Je suis plutôt un DBA. Bien que, d'autre part, le chargement de grands tableaux d'informations pré-préparées ne soit que la tâche de DBA, nest pa?

"Allez ... Faisons-le maintenant", ai-je dit à mes supérieurs et je me suis précipité sur le site de la FIAS en retroussant mes manches.

«Oh! Oui il y a dbf! " J'ai pensé, en me frottant les mains joyeusement, en m'émerveillant de l'absence d'archive zip «de facto» standard, et, au contraire, de la présence d'arj il y a longtemps dans le Bose et d'un désolé ~~propriétaire~~ , qui était ouvert, bien sûr, 7zip [mais qui ne peut toujours pas être ouvert à l'aide de PowerShell Expand -Archive]. C'est-à-dire pure powershell'om vous ne téléchargerez pas et vous ne le déballez pas. Il faut empiler toutes sortes de conneries sur le serveur. Oh bien.

J'ai écrit les outils pour le chargement parallèle en masse de fichiers dbf depuis plusieurs années, donc il ne devrait pas y avoir de problèmes.

J'ai déballé les dbfs, lancé le programme de téléchargement et pendant que les données se chargeaient, j'ai esquissé un script qui collait des plaques séparées «presque similaires» en une seule, selon l'accessoire.

J'ai téléchargé les données, et je voulais déjà aller au bureau de la direction pour secouer, c.t., lauriers, mais le diable m'a tiré pour voir les résultats de l'importation!

- Oups. WTF !!! ???

Les grandes tables étaient chargées normalement, tandis que les petites tables contenaient des escrocs.

Et donc tout cela m'a rendu triste et triste que je me suis courageusement ressaisie et ai pris la procrastination et mes fonctions directes. Pour jouer avec dbf-kami battu - terriblement ne voulait pas.

J'ai tergiversé pendant deux jours, jusqu'à la fin des candidatures, et les autorités se sont à nouveau dressées à l'horizon, avec la question sacramentelle: «Qu'est-ce qu'on va jeter?»

Et, comme il n'y avait rien à répondre, mais que je ne voulais toujours pas jouer avec dbf, j'ai décidé de télécharger FIAS à partir de xml, d'autant plus qu'ils disent, élégant, à la mode, jeune et «dbf est un format mourant».
Sur ce point, laissez le long monologue d'introduction se terminer et passez aux choses sérieuses.

Épigraphe 2:
Oui, c'est aussi possible. :-)

Il a donc été décidé de charger à l'aide de SQLXMLBULKLOAD - une merveilleuse bibliothèque, juste conçue pour le téléchargement en masse de fichiers xml structurés.
Pour l'utiliser, vous devez télécharger et installer la bibliothèque SqlXml 4.0 Service Pack 1 (SP1).

Dans le cas de FIAS, cependant, la «structure» n'est pas particulièrement demandée. Parce que les fichiers là-bas ne sont pas que xml ... ils sont, bien sûr, xml, mais, en fait, ce sont des tableaux plats avec des données, chaque fichier a une table.

Sur le site sql.ru, j'ai trouvé la procédure spXMLBulkLoad de l' utilisateur respecté Mnior , afin de ne pas sortir du serveur SQL, et même pas d'écrire un appel SQLXMLBULKLOAD au CLR.

En voici une version légèrement modifiée:

spXMLBulkLoad

USE [FIAS2] GO /****** Object: StoredProcedure [dbo].[spXMLBulkLoad] Script Date: 13.05.2019 18:05:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --   Ole Automation Procedures: -- EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE; -- : -- EXEC dbo.spXMLBulkLoad 'Z:\Path\Data.xml','Z:\Path\Schema.xsd', 'FIAS', 'Z:\Path\error.xml' CREATE PROCEDURE [dbo].[spXMLBulkLoad] ( @File SysName ,@Schema SysName ,@DataBase SysName = '<DefaultDataBase>' ,@ErrorLog SysName ) AS BEGIN DECLARE @ErrCode Int ,@OLEXMLBulk Int ,@ErrMethod SysName ,@ErrDescript NVarChar(4000) EXEC @ErrCode = sys.sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload', @OLEXMLBulk OUT IF (@ErrCode = 0) BEGIN SET @DataBase = 'Provider=SQLOLEDB;Data Source=.;DataBase=' + @DataBase + ';Integrated Security=SSPI' EXEC @ErrCode = sys.sp_OASetProperty @OLEXMLBulk ,'ConnectionString', @DataBase IF (@ErrCode <> 0) BEGIN SET @ErrMethod = 'ConnectionString' GOTO Error END EXEC @ErrCode = sys.sp_OASetProperty @OLEXMLBulk ,'ErrorLogFile', @ErrorLog IF (@ErrCode <> 0) BEGIN SET @ErrMethod = 'ErrorLogFile' GOTO Error END EXEC @ErrCode = sys.sp_OASetProperty @OLEXMLBulk, 'CheckConstraints', 1 IF (@ErrCode <> 0) BEGIN SET @ErrMethod = 'CheckConstraints' GOTO Error END --EXEC @ErrCode = sys.sp_OASetProperty @OLEXMLBulk, 'ForceTableLock', 1 -- IF (@ErrCode <> 0) -- BEGIN SET @ErrMethod = 'ForceTableLock' -- GOTO Error -- END EXEC @ErrCode = sys.sp_OAMethod @OLEXMLBulk, 'Execute', NULL, @Schema, @File IF (@ErrCode <> 0) BEGIN SET @ErrMethod = 'Execute' DECLARE @Exist Int ,@Error XML ,@SQL NvarChar(max) EXEC master.dbo.xp_FileExist @ErrorLog, @Exist OUT IF (@Exist = 1) BEGIN --  @ErrorLog  Set @SQL = N'SELECT @Error = E.Error + CASE WHEN Right(E.Error,1) <> ''>'' THEN ''lt>'' ELSE '''' END ' + N'FROM OPENROWSET(BULK '''+ @ErrorLog +''', SINGLE_NCLOB) E(Error)' exec sp_executesql @SQL, N'@Error xml OUTPUT', @Error=@Error OUTPUT SELECT @ErrDescript = IsNull(@ErrDescript,'') + E.Error.value('Description[1]','SysName') + ' ' FROM @Error.nodes('/Result/Error')E(Error) SELECT @ErrDescript = IsNull(@ErrDescript,'') + E.Error.value('Description[1]','SysName') + ' ' FROM @Error.nodes('/Error/Record')E(Error) END ELSE GOTO Error END GOTO Destroy Error: EXEC @ErrCode = sys.sp_OAGetErrorInfo @OLEXMLBulk, @ErrorLog OUT, @ErrDescript OUT Destroy: EXEC @ErrCode = sys.sp_OADestroy @OLEXMLBulk END ELSE SELECT @ErrMethod = 'SQLXMLBulkLoad.SQLXMLBulkload' ,@ErrorLog = 'sp_OACreate' ,@ErrDescript = '  OLE ' --   IF (@ErrMethod IS NOT NULL) BEGIN RAISERROR('    "%s"  "%s": %s',18,1, @ErrMethod, @ErrorLog, @ErrDescript) RETURN @@Error END END GO

Cependant, pour effectuer un chargement en masse de xml à l'aide de cette bibliothèque, des schémas xsd annotés sont nécessaires, qui, en fait, indiquent comment et où ce qui est chargé.

Mentionne que de tels schémas existent, «mais seulement d'anciens» - j'ai déjà trouvé sur une douzaine de sites, mais je n'ai jamais trouvé les schémas eux-mêmes. Et s'est mis en colère.

Il n'est pas difficile de modifier le schéma disponible sur le site Internet de la FIAS pour l'importation manuelle des données.
Mais ... au total - il y a 271 champs! Eh bien, c'est combien vous devez vous asseoir et stupide!

J'ai donc décidé de modifier ces schémas automatiquement, en créant en même temps les tables cibles dans la base de données.

SQLXMLBULKLOAD peut créer automatiquement des tables pour les données chargées à partir d'un schéma annoté, mais, d'autre part, si je fais ce schéma, alors pourquoi ne pas créer ces tables moi-même, comme j'en ai besoin?

J'ai téléchargé des schémas xsd sur le site Web de la FIAS et les ai analysés purement visuellement.

Heureusement, ils sont tous du même type, vous pouvez donc créer la base de données cible et modifier les schémas de chargement avec seulement quelques requêtes peu complexes.

1. Créez une base de données FIAS2 vide.
Pourquoi "2"? Eh bien, parce que "1" - il y avait une base de dbf-ok. Peut-être que nous parlerons d'elle plus tard.

Script pour créer une base de données FIAS2

 CREATE DATABASE [FIAS2] CONTAINMENT = NONE ON PRIMARY ( NAME = N'FIAS', FILENAME = N'E:\Data\FIAS1.mdf' , SIZE = 10485760KB , FILEGROWTH = 1048576KB ) LOG ON ( NAME = N'FIAS_log', FILENAME = N'E:\Data\FIAS1_log.ldf' , SIZE = 1048576KB , FILEGROWTH = 131072KB ) GO ALTER DATABASE [FIAS2] SET COMPATIBILITY_LEVEL = 120 GO ALTER DATABASE [FIAS2] SET ANSI_NULL_DEFAULT ON GO ALTER DATABASE [FIAS2] SET ANSI_NULLS ON GO ALTER DATABASE [FIAS2] SET ANSI_PADDING ON GO ALTER DATABASE [FIAS2] SET ANSI_WARNINGS ON GO ALTER DATABASE [FIAS2] SET ARITHABORT OFF GO ALTER DATABASE [FIAS2] SET AUTO_CLOSE OFF GO ALTER DATABASE [FIAS2] SET AUTO_SHRINK OFF GO ALTER DATABASE [FIAS2] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF) GO ALTER DATABASE [FIAS2] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [FIAS2] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [FIAS2] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [FIAS2] SET CONCAT_NULL_YIELDS_NULL ON GO ALTER DATABASE [FIAS2] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [FIAS2] SET QUOTED_IDENTIFIER ON GO ALTER DATABASE [FIAS2] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [FIAS2] SET DISABLE_BROKER GO ALTER DATABASE [FIAS2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [FIAS2] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [FIAS2] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [FIAS2] SET READ_COMMITTED_SNAPSHOT ON GO ALTER DATABASE [FIAS2] SET READ_WRITE GO ALTER DATABASE [FIAS2] SET RECOVERY SIMPLE GO ALTER DATABASE [FIAS2] SET MULTI_USER GO ALTER DATABASE [FIAS2] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [FIAS2] SET TARGET_RECOVERY_TIME = 0 SECONDS GO ALTER DATABASE [FIAS2] SET DELAYED_DURABILITY = DISABLED GO USE [FIAS2] GO IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [FIAS2] MODIFY FILEGROUP [PRIMARY] DEFAULT GO

2. Créons quelques plaques dans cette base de données.

La première plaque contiendra des schémas xsd, et la seconde sera en fait un schéma de données obtenu à partir de ces schémas xsd.

Les schémas xsd sont, en fait, des fichiers xml ordinaires, vous pouvez donc travailler avec eux comme avec les xml-s ordinaires.

 USE [FIAS2] go if OBJECT_ID('dbo.[_FIAS]') is not null drop table dbo.[_FIAS] go if OBJECT_ID('dbo._FIAS_SCHEMAS') is not null drop table dbo.[_FIAS_SCHEMAS] go Create table dbo.[_FIAS_SCHEMAS] (x xml, [table] sysname) go --  :      ,   . insert into dbo.[_FIAS_SCHEMAS] (x, [table]) SELECT x = CAST(BulkColumn AS XML).query('.'), '_ACTSTAT' [table] FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_ACTSTAT_2_250_08_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_ADDROBJ' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_ADDROBJ_2_250_01_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_CENTERST' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_CENTERST_2_250_10_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_CURENTST' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_CURENTST_2_250_07_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_ADDROBJ' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_DEL_ADDROBJ_2_250_15_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_HOUSEINT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_DEL_HOUSEINT_2_250_17_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_HOUSE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_DEL_HOUSE_2_250_16_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_LANDMARK' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_DEL_LANDMARK_2_250_18_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_NORMDOC' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_DEL_NORMDOC_2_250_19_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_ESTSTAT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_ESTSTAT_2_250_13_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_FLATTYPE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_FLATTYPE_2_250_23_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_HOUSEINT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_HOUSEINT_2_250_03_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_HOUSE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_HOUSE_2_250_02_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_HSTSTAT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_HSTSTAT_2_250_12_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_INTVSTAT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_INTVSTAT_2_250_11_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_LANDMARK' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_LANDMARK_2_250_04_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_NDOCTYPE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_NDOCTYPE_2_250_20_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_NORMDOC' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_NORMDOC_2_250_05_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_OPERSTAT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_OPERSTAT_2_250_09_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_ROOMTYPE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_ROOMTYPE_2_250_24_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_ROOM' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_ROOM_2_250_21_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_SOCRBASE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_SOCRBASE_2_250_06_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_STEAD' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_STEAD_2_250_22_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_STRSTAT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_STRSTAT_2_250_14_04_01_01.xsd', SINGLE_BLOB) x --  :  ,       ;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema' as xs) Select a.[table] ,tbl.[root_name] ,tbl.[root_description] ,tbl.[name] ,ROW_NUMBER() over (partition by a.[table] order by 1/0) N ,tbl.[column] ,tbl.[required] ,tbl.[description] ,tbl.[type] ,tbl.[len] ,Case tbl.[type] when N'byte' then N'tinyint' when N'date' then N'date' when N'int' then N'int' when N'string' then Case when tbl.[len] = 36 and (tbl.[column] like N'%ID' or tbl.[column] = N'NORMDOC') then N'uniqueidentifier' when tbl.[len] is Null then N'nvarchar(max)' Else N'nvarchar(' + cast(tbl.[len] as nvarchar(4000)) + N')' END when N'integer' then Case when tbl.[len] > 9 then N'bigint' when tbl.[len] <= 4 then N'smallint' Else N'int' end else N'nvarchar(max)' End [sqltype] ,tnquery('.') [node] into dbo.[_FIAS] from dbo.[_FIAS_SCHEMAS] a Cross apply axnodes('//xs:attribute') t(n) Cross apply (values --     ,    :) ( x.value('(xs:schema[1]/xs:element[1]/@name)', 'nvarchar(255)') ,x.value('(xs:schema[1]/xs:element[1]/xs:annotation/xs:documentation[1]/text())[1]', 'nvarchar(4000)') ,x.value('(xs:schema[1]/xs:element[1]/xs:complexType[1]/xs:sequence[1]/xs:element[1]/@name)', 'nvarchar(255)') ,tnvalue('(./@name)[1]', 'nvarchar(255)') ,tnvalue('(./@use)[1]', 'nvarchar(255)') ,Stuff(Coalesce(tnvalue('(./xs:simpleType/xs:restriction/@base)[1]', 'nvarchar(255)'), tnvalue('(./@type)[1]', 'nvarchar(255)')), 1, 3, '') ,Coalesce(tnvalue('(./xs:simpleType/xs:restriction/xs:length/@value)[1]', 'int') ,tnvalue('(./xs:simpleType/xs:restriction/xs:maxLength/@value)[1]', 'int') ,tnvalue('(./xs:simpleType/xs:restriction/xs:totalDigits/@value)[1]', 'int')) ,Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace(tnvalue('(./xs:annotation/xs:documentation)[1]', 'nvarchar(4000)'), ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') ) ) tbl([root_name], [root_description], [name], [column], [required], [type], [len], [description]) --    Select * from dbo.[_FIAS]

Plus sur demande:

Le premier charge les schémas xsd dans une table sur le serveur, fournissant simultanément à chaque schéma une annotation dans le champ [table] - le nom de la table dans laquelle je veux charger les données de la table correspondante.

Bien sûr, il serait possible de faire en sorte que chaque fois que les schémas changent, ils puissent être téléchargés, glissés dans un dossier et recréer la structure des tables cibles encore et automatiquement, et encore et modifier automatiquement les schémas, mais .k. la structure change extrêmement rarement, la dernière fois qu'elle a changé - déjà en l'an 16 - faire un tel automate est extrêmement paresseux. Mieux vaut passer 30 secondes sur CTRL + C - CTRL + V.

Par conséquent, le chargement dans une plaque avec des schémas est tellement codé en dur et les chemins d'accès aux fichiers téléchargés, ainsi que les noms des tables, sont écrits à la main.

La deuxième requête - extrait des informations sur la structure des tables des schémas. Je n'ai pas pris la peine d'utiliser le 3-nf, mais je l'ai déployé comme une seule table.

Au fait, faites attention à cette pièce (+)

Très souvent, les débutants posent la question: est-il possible de calculer une sorte d'expression complexe dans une requête 1 fois, puis de la réutiliser à plusieurs endroits. Oui tu peux. Comme ça, par exemple:

 from dbo.[_FIAS_SCHEMAS] a Cross apply axnodes('//xs:attribute') t(n) Cross apply (values --     ,    :) ( x.value('(xs:schema[1]/xs:element[1]/@name)', 'nvarchar(255)') , ...      ,         ) ) tbl([root_name], [root_description], [name], [column], [required], [type], [len], [description])

Cela fonctionne bien sûr avec quelques limitations. Mais, en commençant à utiliser, vous comprendrez rapidement de quoi il s'agit.

3. Créez les scripts qui créent les tables FIAS, puis exécutez-les et créez les tables elles-mêmes:

 Declare @schema sysname = N'dbo' Declare @sql nvarchar(max) --     ,    set @sql = ( Select Distinct N';Create table ' + Quotename(@schema) + N'.' + Quotename([table]) + N'(' + Stuff((Select N',' + QUOTENAME(b.[column]) + N' ' + b.[sqltype] + Case b.[required] when 'required' then N' NOT NULL' when 'optional' then N' NULL' End From dbo.[_FIAS] b where b.[table] = a.[table] Order by N ASC For xml path(N''), type ).value(N'.', 'nvarchar(max)'), 1, 1, N'') + N')' from dbo.[_FIAS] a For xml path(N''), type).value(N'.', N'nvarchar(max)') exec (@sql)

Trop paresseux pour faire tout cela - des scripts pour créer des tables et des schémas FIAS annotés seront joints à la fin de l'article.

Une demande de création de tables résultant d'une requête

 ; CREATE TABLE [dbo].[_ACTSTAT] ( [NAME] NVARCHAR(100) NOT NULL ,[ACTSTATID] BIGINT NOT NULL ); CREATE TABLE [dbo].[_ADDROBJ] ( [AOGUID] UNIQUEIDENTIFIER NOT NULL ,[FORMALNAME] NVARCHAR(120) NOT NULL ,[REGIONCODE] NVARCHAR(2) NOT NULL ,[AUTOCODE] NVARCHAR(1) NOT NULL ,[AREACODE] NVARCHAR(3) NOT NULL ,[CITYCODE] NVARCHAR(3) NOT NULL ,[CTARCODE] NVARCHAR(3) NOT NULL ,[PLACECODE] NVARCHAR(3) NOT NULL ,[PLANCODE] NVARCHAR(4) NOT NULL ,[STREETCODE] NVARCHAR(4) NULL ,[EXTRCODE] NVARCHAR(4) NOT NULL ,[SEXTCODE] NVARCHAR(3) NOT NULL ,[OFFNAME] NVARCHAR(120) NULL ,[POSTALCODE] NVARCHAR(6) NULL ,[IFNSFL] NVARCHAR(4) NULL ,[TERRIFNSFL] NVARCHAR(4) NULL ,[IFNSUL] NVARCHAR(4) NULL ,[TERRIFNSUL] NVARCHAR(4) NULL ,[OKATO] NVARCHAR(11) NULL ,[UPDATEDATE] DATE NOT NULL ,[SHORTNAME] NVARCHAR(10) NOT NULL ,[AOLEVEL] BIGINT NOT NULL ,[PARENTGUID] UNIQUEIDENTIFIER NULL ,[AOID] UNIQUEIDENTIFIER NOT NULL ,[PREVID] UNIQUEIDENTIFIER NULL ,[NEXTID] UNIQUEIDENTIFIER NULL ,[CODE] NVARCHAR(17) NULL ,[PLAINCODE] NVARCHAR(15) NULL ,[ACTSTATUS] BIGINT NOT NULL ,[CENTSTATUS] BIGINT NOT NULL ,[OPERSTATUS] BIGINT NOT NULL ,[CURRSTATUS] BIGINT NOT NULL ,[STARTDATE] DATE NOT NULL ,[ENDDATE] DATE NOT NULL ,[NORMDOC] UNIQUEIDENTIFIER NULL ,[LIVESTATUS] TINYINT NOT NULL ,[DIVTYPE] INT NOT NULL ,[OKTMO] NVARCHAR(11) NULL ); CREATE TABLE [dbo].[_CENTERST] ( [CENTERSTID] BIGINT NOT NULL ,[NAME] NVARCHAR(100) NOT NULL ); CREATE TABLE [dbo].[_CURENTST] ( [CURENTSTID] BIGINT NOT NULL ,[NAME] NVARCHAR(100) NOT NULL ); CREATE TABLE [dbo].[_DEL_ADDROBJ] ( [AOGUID] UNIQUEIDENTIFIER NOT NULL ,[FORMALNAME] NVARCHAR(120) NOT NULL ,[REGIONCODE] NVARCHAR(2) NOT NULL ,[AUTOCODE] NVARCHAR(1) NOT NULL ,[AREACODE] NVARCHAR(3) NOT NULL ,[CITYCODE] NVARCHAR(3) NOT NULL ,[CTARCODE] NVARCHAR(3) NOT NULL ,[PLACECODE] NVARCHAR(3) NOT NULL ,[STREETCODE] NVARCHAR(4) NULL ,[EXTRCODE] NVARCHAR(4) NOT NULL ,[PLANCODE] NVARCHAR(4) NOT NULL ,[SEXTCODE] NVARCHAR(3) NOT NULL ,[OFFNAME] NVARCHAR(120) NULL ,[POSTALCODE] NVARCHAR(6) NULL ,[SHORTNAME] NVARCHAR(10) NOT NULL ,[AOLEVEL] BIGINT NOT NULL ,[PARENTGUID] UNIQUEIDENTIFIER NULL ,[AOID] UNIQUEIDENTIFIER NOT NULL ,[PREVID] UNIQUEIDENTIFIER NULL ,[NEXTID] UNIQUEIDENTIFIER NULL ,[CODE] NVARCHAR(17) NULL ,[PLAINCODE] NVARCHAR(15) NULL ,[ACTSTATUS] BIGINT NOT NULL ,[CENTSTATUS] BIGINT NOT NULL ,[OPERSTATUS] BIGINT NOT NULL ,[CURRSTATUS] BIGINT NOT NULL ,[STARTDATE] DATE NOT NULL ,[ENDDATE] DATE NOT NULL ,[NORMDOC] UNIQUEIDENTIFIER NULL ,[LIVESTATUS] TINYINT NOT NULL ,[IFNSFL] NVARCHAR(4) NULL ,[TERRIFNSFL] NVARCHAR(4) NULL ,[IFNSUL] NVARCHAR(4) NULL ,[TERRIFNSUL] NVARCHAR(4) NULL ,[OKATO] NVARCHAR(11) NULL ,[OKTMO] NVARCHAR(11) NULL ,[UPDATEDATE] DATE NOT NULL ); CREATE TABLE [dbo].[_DEL_HOUSE] ( [POSTALCODE] NVARCHAR(6) NULL ,[IFNSFL] NVARCHAR(4) NULL ,[TERRIFNSFL] NVARCHAR(4) NULL ,[IFNSUL] NVARCHAR(4) NULL ,[TERRIFNSUL] NVARCHAR(4) NULL ,[OKATO] NVARCHAR(11) NULL ,[OKTMO] NVARCHAR(11) NULL ,[UPDATEDATE] DATE NOT NULL ,[HOUSENUM] NVARCHAR(20) NULL ,[ESTSTATUS] SMALLINT NOT NULL ,[BUILDNUM] NVARCHAR(10) NULL ,[STRUCNUM] NVARCHAR(10) NULL ,[STRSTATUS] BIGINT NULL ,[HOUSEID] UNIQUEIDENTIFIER NOT NULL ,[HOUSEGUID] UNIQUEIDENTIFIER NOT NULL ,[AOGUID] UNIQUEIDENTIFIER NOT NULL ,[STARTDATE] DATE NOT NULL ,[ENDDATE] DATE NOT NULL ,[STATSTATUS] BIGINT NOT NULL ,[NORMDOC] UNIQUEIDENTIFIER NULL ,[COUNTER] BIGINT NOT NULL ,[CADNUM] NVARCHAR(100) NULL ,[DIVTYPE] INT NOT NULL ); CREATE TABLE [dbo].[_DEL_HOUSEINT] ( [IFNSUL] NVARCHAR(4) NULL ,[TERRIFNSUL] NVARCHAR(4) NULL ,[OKATO] NVARCHAR(11) NULL ,[OKTMO] NVARCHAR(11) NULL ,[UPDATEDATE] DATE NOT NULL ,[INTSTART] BIGINT NOT NULL ,[INTEND] BIGINT NOT NULL ,[POSTALCODE] NVARCHAR(6) NULL ,[IFNSFL] NVARCHAR(4) NULL ,[TERRIFNSFL] NVARCHAR(4) NULL ,[HOUSEINTID] UNIQUEIDENTIFIER NOT NULL ,[INTGUID] UNIQUEIDENTIFIER NOT NULL ,[AOGUID] UNIQUEIDENTIFIER NOT NULL ,[STARTDATE] DATE NOT NULL ,[ENDDATE] DATE NOT NULL ,[INTSTATUS] BIGINT NOT NULL ,[NORMDOC] UNIQUEIDENTIFIER NULL ,[COUNTER] BIGINT NOT NULL ); CREATE TABLE [dbo].[_DEL_LANDMARK] ( [LOCATION] NVARCHAR(500) NOT NULL ,[POSTALCODE] NVARCHAR(6) NULL ,[IFNSFL] NVARCHAR(4) NULL ,[TERRIFNSFL] NVARCHAR(4) NULL ,[IFNSUL] NVARCHAR(4) NULL ,[TERRIFNSUL] NVARCHAR(4) NULL ,[OKATO] NVARCHAR(11) NULL ,[OKTMO] NVARCHAR(11) NULL ,[UPDATEDATE] DATE NOT NULL ,[LANDID] UNIQUEIDENTIFIER NOT NULL ,[LANDGUID] UNIQUEIDENTIFIER NOT NULL ,[AOGUID] UNIQUEIDENTIFIER NOT NULL ,[STARTDATE] DATE NOT NULL ,[ENDDATE] DATE NOT NULL ,[NORMDOC] UNIQUEIDENTIFIER NULL ); CREATE TABLE [dbo].[_DEL_NORMDOC] ( [NORMDOCID] UNIQUEIDENTIFIER NOT NULL ,[DOCNAME] NVARCHAR(max) NULL ,[DOCDATE] DATE NULL ,[DOCNUM] NVARCHAR(20) NULL ,[DOCTYPE] BIGINT NOT NULL ,[DOCIMGID] UNIQUEIDENTIFIER NULL ); CREATE TABLE [dbo].[_ESTSTAT] ( [ESTSTATID] BIGINT NOT NULL ,[NAME] NVARCHAR(20) NOT NULL ,[SHORTNAME] NVARCHAR(20) NULL ); CREATE TABLE [dbo].[_FLATTYPE] ( [SHORTNAME] NVARCHAR(20) NULL ,[FLTYPEID] BIGINT NOT NULL ,[NAME] NVARCHAR(20) NOT NULL ); CREATE TABLE [dbo].[_HOUSE] ( [POSTALCODE] NVARCHAR(6) NULL ,[REGIONCODE] NVARCHAR(2) NULL ,[IFNSFL] NVARCHAR(4) NULL ,[TERRIFNSFL] NVARCHAR(4) NULL ,[IFNSUL] NVARCHAR(4) NULL ,[TERRIFNSUL] NVARCHAR(4) NULL ,[OKATO] NVARCHAR(11) NULL ,[OKTMO] NVARCHAR(11) NULL ,[UPDATEDATE] DATE NOT NULL ,[HOUSENUM] NVARCHAR(20) NULL ,[ESTSTATUS] SMALLINT NOT NULL ,[BUILDNUM] NVARCHAR(10) NULL ,[STRUCNUM] NVARCHAR(10) NULL ,[STRSTATUS] BIGINT NULL ,[HOUSEID] UNIQUEIDENTIFIER NOT NULL ,[HOUSEGUID] UNIQUEIDENTIFIER NOT NULL ,[AOGUID] UNIQUEIDENTIFIER NOT NULL ,[STARTDATE] DATE NOT NULL ,[ENDDATE] DATE NOT NULL ,[STATSTATUS] BIGINT NOT NULL ,[NORMDOC] UNIQUEIDENTIFIER NULL ,[COUNTER] BIGINT NOT NULL ,[CADNUM] NVARCHAR(100) NULL ,[DIVTYPE] INT NOT NULL ); CREATE TABLE [dbo].[_HOUSEINT] ( [OKTMO] NVARCHAR(11) NULL ,[OKATO] NVARCHAR(11) NULL ,[IFNSUL] NVARCHAR(4) NULL ,[TERRIFNSUL] NVARCHAR(4) NULL ,[TERRIFNSFL] NVARCHAR(4) NULL ,[POSTALCODE] NVARCHAR(6) NULL ,[IFNSFL] NVARCHAR(4) NULL ,[ENDDATE] DATE NOT NULL ,[INTSTATUS] BIGINT NOT NULL ,[AOGUID] UNIQUEIDENTIFIER NOT NULL ,[STARTDATE] DATE NOT NULL ,[INTGUID] UNIQUEIDENTIFIER NOT NULL ,[HOUSEINTID] UNIQUEIDENTIFIER NOT NULL ,[INTSTART] BIGINT NOT NULL ,[INTEND] BIGINT NOT NULL ,[UPDATEDATE] DATE NOT NULL ,[NORMDOC] UNIQUEIDENTIFIER NULL ,[COUNTER] BIGINT NOT NULL ); CREATE TABLE [dbo].[_HSTSTAT] ( [HOUSESTID] BIGINT NOT NULL ,[NAME] NVARCHAR(60) NOT NULL ); CREATE TABLE [dbo].[_INTVSTAT] ( [INTVSTATID] BIGINT NOT NULL ,[NAME] NVARCHAR(60) NOT NULL ); CREATE TABLE [dbo].[_LANDMARK] ( [NORMDOC] UNIQUEIDENTIFIER NULL ,[STARTDATE] DATE NOT NULL ,[ENDDATE] DATE NOT NULL ,[LANDGUID] UNIQUEIDENTIFIER NOT NULL ,[AOGUID] UNIQUEIDENTIFIER NOT NULL ,[LANDID] UNIQUEIDENTIFIER NOT NULL ,[LOCATION] NVARCHAR(500) NOT NULL ,[POSTALCODE] NVARCHAR(6) NULL ,[TERRIFNSFL] NVARCHAR(4) NULL ,[IFNSFL] NVARCHAR(4) NULL ,[UPDATEDATE] DATE NOT NULL ,[OKTMO] NVARCHAR(11) NULL ,[OKATO] NVARCHAR(11) NULL ,[IFNSUL] NVARCHAR(4) NULL ,[TERRIFNSUL] NVARCHAR(4) NULL ); CREATE TABLE [dbo].[_NDOCTYPE] ( [NDTYPEID] BIGINT NOT NULL ,[NAME] NVARCHAR(250) NOT NULL ); CREATE TABLE [dbo].[_NORMDOC] ( [NORMDOCID] UNIQUEIDENTIFIER NOT NULL ,[DOCNAME] NVARCHAR(max) NULL ,[DOCDATE] DATE NULL ,[DOCNUM] NVARCHAR(20) NULL ,[DOCTYPE] BIGINT NOT NULL ,[DOCIMGID] UNIQUEIDENTIFIER NULL ); CREATE TABLE [dbo].[_OPERSTAT] ( [OPERSTATID] BIGINT NOT NULL ,[NAME] NVARCHAR(100) NOT NULL ); CREATE TABLE [dbo].[_ROOM] ( [ROOMGUID] UNIQUEIDENTIFIER NOT NULL ,[FLATNUMBER] NVARCHAR(50) NOT NULL ,[FLATTYPE] INT NOT NULL ,[ROOMNUMBER] NVARCHAR(50) NULL ,[ROOMTYPE] INT NULL ,[REGIONCODE] NVARCHAR(2) NOT NULL ,[POSTALCODE] NVARCHAR(6) NULL ,[UPDATEDATE] DATE NOT NULL ,[HOUSEGUID] UNIQUEIDENTIFIER NOT NULL ,[ROOMID] UNIQUEIDENTIFIER NOT NULL ,[PREVID] UNIQUEIDENTIFIER NULL ,[NEXTID] UNIQUEIDENTIFIER NULL ,[STARTDATE] DATE NOT NULL ,[ENDDATE] DATE NOT NULL ,[LIVESTATUS] TINYINT NOT NULL ,[NORMDOC] UNIQUEIDENTIFIER NULL ,[OPERSTATUS] BIGINT NOT NULL ,[CADNUM] NVARCHAR(100) NULL ,[ROOMCADNUM] NVARCHAR(100) NULL ); CREATE TABLE [dbo].[_ROOMTYPE] ( [SHORTNAME] NVARCHAR(20) NULL ,[NAME] NVARCHAR(20) NOT NULL ,[RMTYPEID] BIGINT NOT NULL ); CREATE TABLE [dbo].[_SOCRBASE] ( [LEVEL] BIGINT NOT NULL ,[SCNAME] NVARCHAR(10) NULL ,[SOCRNAME] NVARCHAR(50) NOT NULL ,[KOD_T_ST] NVARCHAR(4) NOT NULL ); CREATE TABLE [dbo].[_STEAD] ( [STEADGUID] UNIQUEIDENTIFIER NOT NULL ,[NUMBER] NVARCHAR(120) NULL ,[REGIONCODE] NVARCHAR(2) NOT NULL ,[POSTALCODE] NVARCHAR(6) NULL ,[IFNSFL] NVARCHAR(4) NULL ,[TERRIFNSFL] NVARCHAR(4) NULL ,[IFNSUL] NVARCHAR(4) NULL ,[TERRIFNSUL] NVARCHAR(4) NULL ,[OKATO] NVARCHAR(11) NULL ,[OKTMO] NVARCHAR(11) NULL ,[UPDATEDATE] DATE NOT NULL ,[PARENTGUID] UNIQUEIDENTIFIER NULL ,[STEADID] UNIQUEIDENTIFIER NOT NULL ,[PREVID] UNIQUEIDENTIFIER NULL ,[NEXTID] UNIQUEIDENTIFIER NULL ,[OPERSTATUS] BIGINT NOT NULL ,[STARTDATE] DATE NOT NULL ,[ENDDATE] DATE NOT NULL ,[NORMDOC] UNIQUEIDENTIFIER NULL ,[LIVESTATUS] TINYINT NOT NULL ,[CADNUM] NVARCHAR(100) NULL ,[DIVTYPE] INT NOT NULL ); CREATE TABLE [dbo].[_STRSTAT] ( [NAME] NVARCHAR(20) NOT NULL ,[SHORTNAME] NVARCHAR(20) NULL ,[STRSTATID] BIGINT NOT NULL )

4. Tant que xsd contient une description normale des tables et des champs, nous l'utilisons et créons une description de la structure dans les propriétés avancées des tables et de leurs champs:

 --       Set @sql = ( Select N'; exec sp_addextendedproperty @name=''description'', @value=N''' + a.[root_description] + N''', @level0type =N''Schema'', @level0name = N''' + @schema + N''', @level1type = N''Table'', @level1name = N''' + a.[table] + N'''' from (Select distinct [table], [root_description] from dbo.[_FIAS]) a for xml path(N''), type ).value(N'.', N'nvarchar(max)') exec (@sql) Set @sql = ( Select N'; exec sp_addextendedproperty @name=''description'', @value=N''' + a.[description] + N''', @level0type =N''Schema'', @level0name = N''' + @schema + N''', @level1type = N''Table'', @level1name = N''' + a.[table] + N''', @level2type = N''Column'', @level2name = N''' + a.[column] + N'''' from dbo.[_FIAS] a for xml path(N''), type ).value(N'.', N'nvarchar(max)') exec (@sql)

Voici le code qui est sorti et exécuté à la suite du travail

 ; EXEC sp_addextendedproperty @name = 'description' ,@value = N'           ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ACTSTAT'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'          ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'           ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_CENTERST'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'          4.0   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_CURENTST'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'          ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'             ,     .   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'           ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'              ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'          ,         ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'           ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ESTSTAT'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'           ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_FLATTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'                ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'           ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'            ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HSTSTAT'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'            ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_INTVSTAT'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'              ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'            ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_NDOCTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'          ,         ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'           ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_OPERSTAT'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'       ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'           ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOMTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'            ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_SOCRBASE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'        ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'           ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STRSTAT' ; EXEC sp_addextendedproperty @name = 'description' ,@value = N'     ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'AOGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'FORMALNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'REGIONCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'AUTOCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'AREACODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'CITYCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'CTARCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'PLACECODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'PLANCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'STREETCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'EXTRCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'SEXTCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'OFFNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'POSTALCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'IFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'TERRIFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'IFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'TERRIFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKATO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'OKATO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'UPDATEDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'SHORTNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'AOLEVEL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'PARENTGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  .  .' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'AOID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'      ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'PREVID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'      ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'NEXTID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'          4.0. ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'CODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'     4.0      (  )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'PLAINCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    .     .      . 0 –   1 -  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'ACTSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'CENTSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    –    (.   OperationStatus): 01 – ; 10 – ; 20 – ; 21 –  ; 30 – ; 31 -     ; 40 –    (); 41 –     ; 42 -        ; 43 -         ; 50 – ; 51 –     ; 60 –    ; 61 –        ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'OPERSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   4 (    )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'CURRSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'STARTDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'ENDDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'LIVESTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  : 0 -   1 - ; 2 - - ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'DIVTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKTMO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'OKTMO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ()' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_CURENTST' ,@level2type = N'Column' ,@level2name = N'CURENTSTID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' (0 - , 1-50, 2-98 –  ( 51), 51 - , 99 - )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_CURENTST' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_FLATTYPE' ,@level2type = N'Column' ,@level2name = N'SHORTNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_FLATTYPE' ,@level2type = N'Column' ,@level2name = N'FLTYPEID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_FLATTYPE' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HSTSTAT' ,@level2type = N'Column' ,@level2name = N'HOUSESTID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HSTSTAT' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ()' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_NDOCTYPE' ,@level2type = N'Column' ,@level2name = N'NDTYPEID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_NDOCTYPE' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ()' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_OPERSTAT' ,@level2type = N'Column' ,@level2name = N'OPERSTATID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' 01 – ; 10 – ; 20 – ; 21 –  ; 30 – ; 31 -     ; 40 –    (); 41 –     ; 42 -        ; 43 -         ; 50 – ; 51 –     ; 60 –    ; 61 –       ; 70 –    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_OPERSTAT' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'     ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'AOGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'FORMALNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'REGIONCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'AUTOCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'AREACODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'CITYCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'CTARCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'PLACECODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'STREETCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'EXTRCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'PLANCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'SEXTCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'OFFNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'POSTALCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'SHORTNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'AOLEVEL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'PARENTGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  .  .' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'AOID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'      ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'PREVID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'      ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'NEXTID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'          4.0. ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'CODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'     4.0      (  )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'PLAINCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    .     .      . 0 –   1 -  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'ACTSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'CENTSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    –    (.   OperationStatus): 01 – ; 10 – ; 20 – ; 21 –  ; 30 – ; 31 -     ; 40 –    (); 41 –     ; 42 -        ; 43 -         ; 50 – ; 51 –     ; 60 –    ; 61 –        ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'OPERSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   4 (    )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'CURRSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'STARTDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'ENDDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'LIVESTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'IFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'TERRIFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'IFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'TERRIFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKATO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'OKATO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKTMO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'OKTMO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_ADDROBJ' ,@level2type = N'Column' ,@level2name = N'UPDATEDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'IFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'TERRIFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKATO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'OKATO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKTMO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'OKTMO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'UPDATEDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'INTSTART'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'INTEND'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'POSTALCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'IFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'TERRIFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'HOUSEINTID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'INTGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    (, ,    ..)' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'AOGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'STARTDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'ENDDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  (, , )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'INTSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'     4' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'COUNTER'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_NORMDOC' ,@level2type = N'Column' ,@level2name = N'NORMDOCID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_NORMDOC' ,@level2type = N'Column' ,@level2name = N'DOCNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_NORMDOC' ,@level2type = N'Column' ,@level2name = N'DOCDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_NORMDOC' ,@level2type = N'Column' ,@level2name = N'DOCNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_NORMDOC' ,@level2type = N'Column' ,@level2name = N'DOCTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ( )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_NORMDOC' ,@level2type = N'Column' ,@level2name = N'DOCIMGID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKTMO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'OKTMO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKATO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'OKATO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'IFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'TERRIFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'TERRIFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'POSTALCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'IFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'ENDDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  (, , )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'INTSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    (, ,    ..)' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'AOGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'STARTDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'INTGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'HOUSEINTID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'INTSTART'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'INTEND'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'UPDATEDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'     4' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSEINT' ,@level2type = N'Column' ,@level2name = N'COUNTER'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  (, , )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_INTVSTAT' ,@level2type = N'Column' ,@level2name = N'INTVSTATID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_INTVSTAT' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'STARTDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'ENDDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'LANDGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    (, ,    ..)' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'AOGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'LANDID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'LOCATION'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'POSTALCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'TERRIFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'IFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'UPDATEDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKTMO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'OKTMO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKATO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'OKATO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'IFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_LANDMARK' ,@level2type = N'Column' ,@level2name = N'TERRIFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_NORMDOC' ,@level2type = N'Column' ,@level2name = N'NORMDOCID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_NORMDOC' ,@level2type = N'Column' ,@level2name = N'DOCNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_NORMDOC' ,@level2type = N'Column' ,@level2name = N'DOCDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_NORMDOC' ,@level2type = N'Column' ,@level2name = N'DOCNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_NORMDOC' ,@level2type = N'Column' ,@level2name = N'DOCTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ( )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_NORMDOC' ,@level2type = N'Column' ,@level2name = N'DOCIMGID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOMTYPE' ,@level2type = N'Column' ,@level2name = N'SHORTNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOMTYPE' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOMTYPE' ,@level2type = N'Column' ,@level2name = N'RMTYPEID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_SOCRBASE' ,@level2type = N'Column' ,@level2name = N'LEVEL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_SOCRBASE' ,@level2type = N'Column' ,@level2name = N'SCNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_SOCRBASE' ,@level2type = N'Column' ,@level2name = N'SOCRNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_SOCRBASE' ,@level2type = N'Column' ,@level2name = N'KOD_T_ST'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' 0 –   1 –  (    ) ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ACTSTAT' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ()' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ACTSTAT' ,@level2type = N'Column' ,@level2name = N'ACTSTATID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_CENTERST' ,@level2type = N'Column' ,@level2name = N'CENTERSTID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_CENTERST' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'POSTALCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'IFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'TERRIFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'IFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'TERRIFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKATO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'OKATO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKTMO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'OKTMO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'UPDATEDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'HOUSENUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'ESTSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'BUILDNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'STRUCNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'STRSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'HOUSEID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'HOUSEGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'Guid    (, ,    ..)' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'AOGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'STARTDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'ENDDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'STATSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'     4' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'COUNTER'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'CADNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' : 0 -   1 - ; 2 - -' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_HOUSE' ,@level2type = N'Column' ,@level2name = N'DIVTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ESTSTAT' ,@level2type = N'Column' ,@level2name = N'ESTSTATID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ESTSTAT' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ESTSTAT' ,@level2type = N'Column' ,@level2name = N'SHORTNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'     ( )' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'STEADGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'NUMBER'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'REGIONCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'POSTALCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'IFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'TERRIFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'IFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'TERRIFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKATO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'OKATO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKTMO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'OKTMO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'UPDATEDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'PARENTGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  .  .' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'STEADID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'      ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'PREVID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'      ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'NEXTID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    –    (.   OperationStatus): 01 – ; 10 – ; 20 – ; 21 –  ; 30 – ; 31 -     ; 40 –    (); 41 –     ; 42 -        ; 43 -         ; 50 – ; 51 –     ; 60 –    ; 61 –        ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'OPERSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'STARTDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'ENDDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'LIVESTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'CADNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' : 0 -   1 - ; 2 - -' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STEAD' ,@level2type = N'Column' ,@level2name = N'DIVTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'LOCATION'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'POSTALCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'IFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'TERRIFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'IFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'TERRIFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKATO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'OKATO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKTMO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'OKTMO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'UPDATEDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'LANDID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'LANDGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    (, ,    ..)' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'AOGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'STARTDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'ENDDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_DEL_LANDMARK' ,@level2type = N'Column' ,@level2name = N'NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'POSTALCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'REGIONCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'IFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'TERRIFNSFL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'IFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'TERRIFNSUL'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKATO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'OKATO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'OKTMO' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'OKTMO'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'UPDATEDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'HOUSENUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'ESTSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'BUILDNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'STRUCNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'STRSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'HOUSEID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'HOUSEGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'Guid    (, ,    ..)' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'AOGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'STARTDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'ENDDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'STATSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'     4' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'COUNTER'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'CADNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' : 0 -   1 - ; 2 - -' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_HOUSE' ,@level2type = N'Column' ,@level2name = N'DIVTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'     ()' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'ROOMGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'FLATNUMBER'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'FLATTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'ROOMNUMBER'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'ROOMTYPE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'REGIONCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'POSTALCODE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'UPDATEDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ()' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'HOUSEGUID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  .  .' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'ROOMID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'      ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'PREVID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'      ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'NEXTID'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'STARTDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'ENDDATE'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'   ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'LIVESTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'NORMDOC'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    –    (.   OperationStatus): 01 – ; 10 – ; 20 – ; 21 –  ; 30 – ; 31 -     ; 40 –    (); 41 –     ; 42 -        ; 43 -         ; 50 – ; 51 –     ; 60 –    ; 61 –        ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'OPERSTATUS'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'  ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'CADNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'    ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_ROOM' ,@level2type = N'Column' ,@level2name = N'ROOMCADNUM'; EXEC sp_addextendedproperty @name = 'description' ,@value = N'' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STRSTAT' ,@level2type = N'Column' ,@level2name = N'NAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STRSTAT' ,@level2type = N'Column' ,@level2name = N'SHORTNAME'; EXEC sp_addextendedproperty @name = 'description' ,@value = N' ' ,@level0type = N'Schema' ,@level0name = N'dbo' ,@level1type = N'Table' ,@level1name = N'_STRSTAT' ,@level2type = N'Column' ,@level2name = N'STRSTATID'

5. Nous modifions les schémas eux-mêmes:

 --      update a Set x.modify ('declare namespace xs="http://www.w3.org/2001/XMLSchema"; insert <xs:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="./sqltypes.xsd" /> as first into (xs:schema)[1]') from dbo.[_FIAS_SCHEMAS] a --    update a Set x.modify('declare namespace ss="urn:schemas-microsoft-com:mapping-schema"; insert ( attribute ss:is-constant {"1"} ) into (/xs:schema[1]/xs:element[1])') from dbo.[_FIAS_SCHEMAS] a --  ,         update a Set x.modify('declare namespace ss="urn:schemas-microsoft-com:mapping-schema"; insert ( attribute ss:relation {sql:column("a.table")} ) into (xs:schema[1]/xs:element[1]/xs:complexType[1]/xs:sequence[1]/xs:element[1])') from dbo.[_FIAS_SCHEMAS] a --     -- ..  1       -    Declare @table nvarchar(4000) Declare @column nvarchar(4000) Declare cur CURSOR LOCAL FORWARD_ONLY for Select [table], [column] from dbo.[_FIAS] Open cur FETCH NEXT FROM cur into @table, @column WHILE @@FETCH_STATUS = 0 BEGIN update a Set x.modify('declare namespace ss="urn:schemas-microsoft-com:mapping-schema"; insert ( attribute ss:field {sql:column("t.column")} ,attribute ss:datatype {sql:column("b.sqltype")} ) into (//xs:attribute[@name = sql:column("b.column")])[1]') from dbo.[_FIAS_SCHEMAS] a inner join dbo.[_FIAS] b on a.[table] = b.[table] cross apply (Values(QUOTENAME(b.[column]))) t([column]) Where b.[table] = @table and b.[column] = @column FETCH NEXT FROM cur into @table, @column END CLOSE cur; DEALLOCATE cur;

6. Enfin, déchargez le schéma xsd sur le disque pour une utilisation future:

 --   --  ,     - ! Set @sql = ( Select N'; exec xp_cmdshell ''bcp "Select [x] from [FIAS2].[dbo].[_FIAS_SCHEMAS] where [table] = ''''' + a.[table] + '''''" queryout "c:\files\FIAS\' + a.[table] + '.xsd" -S . -T -w''' from dbo.[_FIAS_SCHEMAS] a for xml path(N''), type ).value(N'.', N'nvarchar(max)') exec (@sql)

C’est tout! :-)))

Voici l'intégralité de la requête qui génère des tables, leurs descriptions, modifie les schémas et les télécharge sur le disque:

 USE [FIAS2] go if OBJECT_ID('dbo.[_FIAS]') is not null drop table dbo.[_FIAS] go if OBJECT_ID('dbo._FIAS_SCHEMAS') is not null drop table dbo.[_FIAS_SCHEMAS] go Declare @schema sysname = N'dbo' Declare @sql nvarchar(max) Create table dbo.[_FIAS_SCHEMAS] (x xml, [table] sysname) insert into dbo.[_FIAS_SCHEMAS] (x, [table]) SELECT x = CAST(BulkColumn AS XML).query('.'), '_ACTSTAT' [table] FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_ACTSTAT_2_250_08_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_ADDROBJ' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_ADDROBJ_2_250_01_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_CENTERST' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_CENTERST_2_250_10_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_CURENTST' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_CURENTST_2_250_07_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_ADDROBJ' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_DEL_ADDROBJ_2_250_15_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_HOUSEINT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_DEL_HOUSEINT_2_250_17_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_HOUSE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_DEL_HOUSE_2_250_16_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_LANDMARK' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_DEL_LANDMARK_2_250_18_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_NORMDOC' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_DEL_NORMDOC_2_250_19_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_ESTSTAT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_ESTSTAT_2_250_13_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_FLATTYPE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_FLATTYPE_2_250_23_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_HOUSEINT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_HOUSEINT_2_250_03_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_HOUSE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_HOUSE_2_250_02_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_HSTSTAT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_HSTSTAT_2_250_12_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_INTVSTAT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_INTVSTAT_2_250_11_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_LANDMARK' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_LANDMARK_2_250_04_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_NDOCTYPE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_NDOCTYPE_2_250_20_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_NORMDOC' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_NORMDOC_2_250_05_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_OPERSTAT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_OPERSTAT_2_250_09_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_ROOMTYPE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_ROOMTYPE_2_250_24_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_ROOM' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_ROOM_2_250_21_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_SOCRBASE' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_SOCRBASE_2_250_06_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_STEAD' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_STEAD_2_250_22_04_01_01.xsd', SINGLE_BLOB) x Union all SELECT x = CAST(BulkColumn AS XML).query('.'), '_STRSTAT' FROM OPENROWSET(BULK '\\s01sq03\c$\files\FIAS\AS_STRSTAT_2_250_14_04_01_01.xsd', SINGLE_BLOB) x ;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema' as xs) Select a.[table] ,tbl.[root_name] ,tbl.[root_description] ,tbl.[name] ,ROW_NUMBER() over (partition by a.[table] order by 1/0) N ,tbl.[column] ,tbl.[required] ,tbl.[description] ,tbl.[type] ,tbl.[len] ,Case tbl.[type] when N'byte' then N'tinyint' when N'date' then N'date' when N'int' then N'int' when N'string' then Case when tbl.[len] = 36 and (tbl.[column] like N'%ID' or tbl.[column] = N'NORMDOC') then N'uniqueidentifier' when tbl.[len] is Null then N'nvarchar(max)' Else N'nvarchar(' + cast(tbl.[len] as nvarchar(4000)) + N')' END when N'integer' then Case when tbl.[len] > 9 then N'bigint' when tbl.[len] <= 4 then N'smallint' Else N'int' end else N'nvarchar(max)' End [sqltype] ,tnquery('.') [node] into dbo.[_FIAS] from dbo.[_FIAS_SCHEMAS] a Cross apply axnodes('//xs:attribute') t(n) Cross apply (values --     ,    :) ( x.value('(xs:schema[1]/xs:element[1]/@name)', 'nvarchar(255)') ,x.value('(xs:schema[1]/xs:element[1]/xs:annotation/xs:documentation[1]/text())[1]', 'nvarchar(4000)') ,x.value('(xs:schema[1]/xs:element[1]/xs:complexType[1]/xs:sequence[1]/xs:element[1]/@name)', 'nvarchar(255)') ,tnvalue('(./@name)[1]', 'nvarchar(255)') ,tnvalue('(./@use)[1]', 'nvarchar(255)') ,Stuff(Coalesce(tnvalue('(./xs:simpleType/xs:restriction/@base)[1]', 'nvarchar(255)'), tnvalue('(./@type)[1]', 'nvarchar(255)')), 1, 3, '') ,Coalesce(tnvalue('(./xs:simpleType/xs:restriction/xs:length/@value)[1]', 'int') ,tnvalue('(./xs:simpleType/xs:restriction/xs:maxLength/@value)[1]', 'int') ,tnvalue('(./xs:simpleType/xs:restriction/xs:totalDigits/@value)[1]', 'int')) ,Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace(tnvalue('(./xs:annotation/xs:documentation)[1]', 'nvarchar(4000)'), ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') ) ) tbl([root_name], [root_description], [name], [column], [required], [type], [len], [description]) --    Select * from dbo.[_FIAS] --     ,    set @sql = ( Select Distinct N';Create table ' + Quotename(@schema) + N'.' + Quotename([table]) + N'(' + Stuff((Select N',' + QUOTENAME(b.[column]) + N' ' + b.[sqltype] + Case b.[required] when 'required' then N' NOT NULL' when 'optional' then N' NULL' End From dbo.[_FIAS] b where b.[table] = a.[table] Order by N ASC For xml path(N''), type ).value(N'.', 'nvarchar(max)'), 1, 1, N'') + N')' from dbo.[_FIAS] a For xml path(N''), type).value(N'.', N'nvarchar(max)') exec (@sql) --       Set @sql = ( Select N'; exec sp_addextendedproperty @name=''description'', @value=N''' + a.[root_description] + N''', @level0type =N''Schema'', @level0name = N''' + @schema + N''', @level1type = N''Table'', @level1name = N''' + a.[table] + N'''' from (Select distinct [table], [root_description] from dbo.[_FIAS]) a for xml path(N''), type ).value(N'.', N'nvarchar(max)') exec (@sql) Set @sql = ( Select N'; exec sp_addextendedproperty @name=''description'', @value=N''' + a.[description] + N''', @level0type =N''Schema'', @level0name = N''' + @schema + N''', @level1type = N''Table'', @level1name = N''' + a.[table] + N''', @level2type = N''Column'', @level2name = N''' + a.[column] + N'''' from dbo.[_FIAS] a for xml path(N''), type ).value(N'.', N'nvarchar(max)') exec (@sql) --      update a Set x.modify ('declare namespace xs="http://www.w3.org/2001/XMLSchema"; insert <xs:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="./sqltypes.xsd" /> as first into (xs:schema)[1]') from dbo.[_FIAS_SCHEMAS] a --    update a Set x.modify('declare namespace ss="urn:schemas-microsoft-com:mapping-schema"; insert ( attribute ss:is-constant {"1"} ) into (/xs:schema[1]/xs:element[1])') from dbo.[_FIAS_SCHEMAS] a --  ,         update a Set x.modify('declare namespace ss="urn:schemas-microsoft-com:mapping-schema"; insert ( attribute ss:relation {sql:column("a.table")} ) into (xs:schema[1]/xs:element[1]/xs:complexType[1]/xs:sequence[1]/xs:element[1])') from dbo.[_FIAS_SCHEMAS] a --     -- ..  1       -    Declare @table nvarchar(4000) Declare @column nvarchar(4000) Declare cur CURSOR LOCAL FORWARD_ONLY for Select [table], [column] from dbo.[_FIAS] Open cur FETCH NEXT FROM cur into @table, @column WHILE @@FETCH_STATUS = 0 BEGIN update a Set x.modify('declare namespace ss="urn:schemas-microsoft-com:mapping-schema"; insert ( attribute ss:field {sql:column("t.column")} ,attribute ss:datatype {sql:column("b.sqltype")} ) into (//xs:attribute[@name = sql:column("b.column")])[1]') from dbo.[_FIAS_SCHEMAS] a inner join dbo.[_FIAS] b on a.[table] = b.[table] cross apply (Values(QUOTENAME(b.[column]))) t([column]) Where b.[table] = @table and b.[column] = @column FETCH NEXT FROM cur into @table, @column END CLOSE cur; DEALLOCATE cur; --   --  ,     - ! Set @sql = ( Select N'; exec xp_cmdshell ''bcp "Select [x] from [FIAS2].[dbo].[_FIAS_SCHEMAS] where [table] = ''''' + a.[table] + '''''" queryout "c:\files\FIAS\' + a.[table] + '.xsd" -S . -T -w''' from dbo.[_FIAS_SCHEMAS] a for xml path(N''), type ).value(N'.', N'nvarchar(max)') exec (@sql)

Maintenant, il ne reste plus qu'à appeler la procédure de téléchargement pour chaque fichier xml avec le schéma correspondant (les fichiers décompressés eux-mêmes sont stockés dans le dossier e: \ tmp, et nous avons téléchargé les schémas dans c: \ files \ FIAS):

 USE FIAS2 GO Declare @sql nvarchar(max) if OBJECT_ID('tempdb..#t') is not null drop table #t Create table #t ([output] sysname NULL) insert into #t exec xp_cmdshell 'dir e:\tmp /B' ;With load_data as ( Select '_' + txvalue('(a[2])', 'nvarchar(255)') [table] ,txvalue('(a[3])', 'date') [fias_date] ,txvalue('(a[4])', 'uniqueidentifier') [fias_guid] ,a.[output] [file_name] from #ta cross apply (VALUES(Cast('<a>' + Replace(Replace(Replace(Replace(a.[output],'DEL_', 'DEL$'), '_', '</a><a>'), '.', '</a><a>'), '$', '_') + '</a>' as xml))) t(x) Where [output] like 'AS[_]%.xml' ) Select @sql = (Select N';exec [dbo].[spXMLBulkLoad] ''e:\tmp\' + a.[file_name] + ''', ''c:\files\FIAS\' + b.[table] + '.xsd'', ''FIAS2'', ''e:\tmp\err.xml''' from load_data a inner join [dbo].[_FIAS_SCHEMAS] b on a.[table] = b.[table] For xml path(''), type).value(N'.', N'nvarchar(max)') Exec (@sql)

…

, - .

, , !
!
, . .

5 (sic!!!). .

- 3 /, .

3 , — 8 . , , 60 .
— , , , .

Veuillez noter que le chargement se fait en tas. Et seulement alors, il est censé créer des index clusterisés pour l'un de leurs AOID.

Parce que GUID, , , — , - 50, . , .

( _ ). , , , , , Alter table switch.

.
, int, tinyint, nvarchar , , .
:-(.

, , , [dbo].[_FIAS], , , .

, , , dbf, , , dbf. .

dbf 8 1 , , .

… , , , … … , data tier application, , , SSIS.

.

→ Scripts et schémas xsd

Liens connexes:

→ FIAS La

photo avec le chien n'est pas la mienne :-)

Chargement de FIAS dans la base de données sur MSSQLSERVER par des moyens improvisés (SQLXMLBULKLOAD). Comment cela (probablement) n'a pas besoin d'être fait

More articles: