Încărcarea FIAs în baza de date pe MSSQLSERVER cu mijloace improvizate (SQLXMLBULKLOAD). Cum nu ar trebui (probabil) să facem asta

Epigraful:
„Când ai un ciocan în mână, totul din jur îți pare cuie.”

Cu ceva timp în urmă, cred că – vinerea trecută, conducerea îngrijorată se întreba ce fac eu în vremea liberă și contemplând pisici și pisicuțe.

— Nu vrei să încarci FIAS, prietene! – a spus conducerea. – Căci procesul de încărcare nu pare să placă diviziilor noastre de afaceri. Zic că durează mult, încarcă serverul de produse, și tipu care a scris procesul de încărcare a demisionat zilele acestea, acum vreo trei ani.
Mai mult, totul trebuie rescris de mult, așa că ia-ți puțin timp, creează-ți o bază de date și asigură-te de încărcarea periodică a FIAS-ului. Toate, cum s-ar spune, nu te rețin!

Trebuie spus că eu am o relație vagă cu programarea, fiind mai degrabă DBA. Deși, pe de altă parte, încărcarea unor mari volume de informații prelucrate este exact sarcina unui DBA, nest pa?

— Hai, făcând! – a spus eu conducerii, apucându-mă de site-ul FIAS-ului, suflecându-mi mânecile.

„O! Dar aici sunt dbf!” – m-am gândit, frecându-mi mâinile de bucurie, mirându-mă în același timp de absența arhivelor zip „de facto”, și, dimpotrivă, prezența vechiului arj, și a proprietarului, desigur, deschis, 7zip [dar care tot nu poate fi desfăcut cu powershell Expand-Archive]. Adică, cu powershell curat, nu poți descărca sau dezarhiva. Va trebui să car pe server tot felul de prostii. Eh, nu-i nimic.

Instrumentul pentru încărcarea în masă și paralelă a fișierelor dbf l-am scris cu ceva timp în urmă, astfel că nu ar trebui să apară probleme.

Am desfăcut dbf-urile, am lansat programul de încărcare, și în timp ce datele se încărcau, am schițat un mic script care combina „aproape-într-ale” tabele în unul, în funcție de apartenență.

Am încărcat datele și deja voiam să merg în biroul conducerii să-mi aduc mulțumiri, t. s., laudele, dar mă apucă naiba să verific rezultatele importului!

— Ups. WTF!!!???

Tabelele mari s-au încărcat normal, dar cele mici – conțineau crakozebre.

Și așa, m-a cuprins o tristețe și o melancolie atât de profundă încât m-am adunat cu curaj și m-am apucat de procrastinare și de îndatoririle mele directe. Nu îmi plăcea deloc să mă ocup de dbf-urile defecte.

Am procrastinat timp de două zile, până când cererile s-au terminat și pe orizont a apărut din nou șefia, cu întrebarea sacramentală „Ce se întâmplă, suntem leneși?”.

Și, cum nu era nimic de răspuns, iar a mă ocupa de dbf nu îmi mai plăcea, am decis să încarc FIAS din xml, mai ales că, după cum se spune, este elegant, modern, tineresc, iar „dbf este un format pe cale de dispariție”.
Aici, permiteți-mi să închei acest monolog introductiv prelungit și să trec la subiect.

Epigraf 2:
Da, se poate și așa. 🙂

Așadar, s-a decis să fie încărcat cu ajutorul SQLXMLBULKLOAD – o bibliotecă minunată, creată exact pentru încărcarea masivă (bulk) a fișierelor xml structurale.
Pentru a o folosi, trebuie descărcați să instalați biblioteca SqlXml 4.0 Service Pack 1 (SP1).

În cazul FIAS-ului, totuși, „structurarea” nu este foarte necesară. Deoarece fișierele nu sunt tocmai xml... sunt, desigur, xml, dar, în esență – sunt tabele plate cu date, fiecare fișier având o tabelă.

Pe site-ul sql.ru am găsit procedura spXMLBulkLoad a respectatului utilizator Mnior, pentru a nu depăși limitele serverului SQL și pentru a nu scrie nici măcar apeluri la SQLXMLBULKLOAD în CLR.

Iată o variantă ușor modificată a acesteia:

spXMLBulkLoad

UTILIZA [FIAS2] GO /****** Obiect: Procedură Stocată [dbo].[spXMLBulkLoad] Data Scriptului: 13.05.2019 18:05:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Este necesar să permiteți Procedurile de Automatizare Ole: -- EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE; -- Exemplu: -- EXEC dbo.spXMLBulkLoad 'Z:PathData.xml','Z:PathSchema.xsd', 'FIAS', 'Z:Patherror.xml' CREATE PROCEDURE [dbo].[spXMLBulkLoad] ( @File SysName, @Schema SysName, @DataBase SysName = '', @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_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 -- Din fișierul @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 = 'Eroare la crearea obiectului OLE' -- Afișarea erorilor IF (@ErrMethod IS NOT NULL) BEGIN RAISERROR('Eroare la executarea metodei "%s" în "%s": %s',18,1, @ErrMethod, @ErrorLog, @ErrDescript) RETURN @@Error END END GO

Cu toate acestea, pentru a efectua încărcarea în masă a XML-ului folosind această bibliotecă, sunt necesare scheme XSD annotate, care specifică exact cum și unde se încarcă datele.

Mențiuni că astfel de scheme există, „dar doar cele vechi” — am găsit pe vreo zece site-uri, dar nu am găsit nicăieri schemele în sine. Și m-am supărat.

Să modifici schema existentă pe site-ul FIAS pentru a importa datele manual – nu este complicat.
Dar… în total – sunt 271 de câmpuri! Cât de mult trebuie să stau și să mă gândesc la asta!

Așadar, am decis să modific aceste scheme în mod automat, în același timp creând tabelele țintă în baza de date.

SQLXMLBULKLOAD poate crea automat tabele pentru datele încărcate dintr-o schemă annotată, dar, pe de altă parte, dacă voi face această schemă, de ce să nu creez aceste tabele eu însumi, așa cum am nevoie?

Am descărcat schemele xsd de pe site-ul FIAS și le-am analizat vizual.

Din fericire, toate sunt uniforme, așa că pot crea baza de date țintă și modifica schemele pentru încărcare folosind doar câteva interogări nu foarte complicate.

1. Vom crea o bază de date goală FIAS2.
De ce "2"? Păi, pentru că "1" a fost baza din dbf-uri. Poate că vom vorbi despre ea mai târziu.

Scriptul pentru crearea bazei de date FIAS2

CREATE DATABASE [FIAS2]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'FIAS', FILENAME = N'E:DataFIAS1.mdf' , SIZE = 10485760KB , FILEGROWTH = 1048576KB )
 LOG ON 
( NAME = N'FIAS_log', FILENAME = N'E:DataFIAS1_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. Vom crea câteva tabele în această bază.

Prima tabelă va conține schemele xsd, iar a doua va reprezenta efectiv schema de date obținută din aceste scheme xsd.

Schemele xsd sunt, de fapt, fișiere xml obișnuite, așa că se pot manipula ca orice fișiere xml.

UTILIZA [FIAS2]
mergiți

if OBJECT_ID('dbo.[_FIAS]') is not null 
	drop table dbo.[_FIAS]
mergiți

if OBJECT_ID('dbo._FIAS_SCHEMAS') is not null 
	drop table dbo.[_FIAS_SCHEMAS]
mergiți

Crează tabel dbo.[_FIAS_SCHEMAS] (x xml, [table "" not found \/]

 sysname)
mergiți

-- Prima interogare: Vom încărca scheme în tabel pe server pentru manipulări ulterioare.
insert into dbo.[_FIAS_SCHEMAS] (x, [table "" not found \/]

)
SELECT x = CAST(BulkColumn AS XML).query('.'), '_ACTSTAT' [table "" not found \/]

 DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_ACTSTAT_2_250_08_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_ADDROBJ' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_ADDROBJ_2_250_01_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_CENTERST' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_CENTERST_2_250_10_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_CURENTST' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_CURENTST_2_250_07_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_ADDROBJ' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_ADDROBJ_2_250_15_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_HOUSEINT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_HOUSEINT_2_250_17_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_HOUSE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_HOUSE_2_250_16_04_01_01.xsd', SINGLE_BLOB) x
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_LANDMARK' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_LANDMARK_2_250_18_04_01_01.xsd', SINGLE_BLOB) x
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_NORMDOC' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_NORMDOC_2_250_19_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_ESTSTAT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_ESTSTAT_2_250_13_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_FLATTYPE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_FLATTYPE_2_250_23_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_HOUSEINT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_HOUSEINT_2_250_03_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_HOUSE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_HOUSE_2_250_02_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_HSTSTAT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_HSTSTAT_2_250_12_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_INTVSTAT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_INTVSTAT_2_250_11_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_LANDMARK' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_LANDMARK_2_250_04_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_NDOCTYPE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_NDOCTYPE_2_250_20_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_NORMDOC' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_NORMDOC_2_250_05_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_OPERSTAT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_OPERSTAT_2_250_09_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_ROOMTYPE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_ROOMTYPE_2_250_24_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_ROOM' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_ROOM_2_250_21_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_SOCRBASE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_SOCRBASE_2_250_06_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_STEAD' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_STEAD_2_250_22_04_01_01.xsd', SINGLE_BLOB) x 
Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_STRSTAT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_STRSTAT_2_250_14_04_01_01.xsd', SINGLE_BLOB) x 

 
-- A doua interogare: Desfacem schemele și creăm un tabel de lucru cu metadatele
;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema' as xs) 
Select a.[table "" not found \/]


  ,tbl.[root_name]
  ,tbl.[root_description]
  ,tbl.[name]
  ,ROW_NUMBER() over (partition by a.[table “” not found

 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]
  ,t.n.query('.') [node]
into dbo.[_FIAS]
from dbo.[_FIAS_SCHEMAS] a
	Cross apply a.x.nodes('//' + '
			(values -- unele câmpuri sunt folosite de mai multe ori, așa că vom face așa :) 
			(
				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)')
				,t.n.value('(.//@name)[1]', 'nvarchar(255)')
				,t.n.value('(.//@use)[1]', 'nvarchar(255)') 
				,Stuff(Coalesce(t.n.value('(.//xs:simpleType/xs:restriction/@base)[1]', 'nvarchar(255)'), t.n.value('(.//@type)[1]', 'nvarchar(255)')), 1, 3, '')
				,Coalesce(t.n.value('(.//xs:simpleType/xs:restriction/xs:length/@value)[1]', 'int')
							,t.n.value('(.//xs:simpleType/xs:restriction/xs:maxLength/@value)[1]', 'int')
							,t.n.value('(.//xs:simpleType/xs:restriction/xs:totalDigits/@value)[1]', 'int'))
				,Replace(
				 Replace(
				 Replace(
				 Replace(
				 Replace(
				 Replace(
				 Replace(
				 Replace(
				 Replace(t.n.value('(.//xs:annotation/xs:documentation)[1]', 'nvarchar(4000)'), '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ') 
			)
			) tbl([root_name], [root_description], [name], [column], [required], [type], [len], [description])

-- Să aruncăm o privire asupra rezultatului
Select * from dbo.[_FIAS]

Mai multe detalii despre interogări:

Prima – încarcă schemele xsd în tabelul de pe server, în același timp furnizând fiecărei scheme o notă în câmpul [table "" not found \/]
– numele tabelului în care vreau să încarc datele din tabelul corespunzător.

Desigur, s-ar fi putut face în așa fel încât, de fiecare dată când schemele se schimbă, acestea să poată fi descărcate, puse într-un folder și structura tabelelor țintă să fie recreată automat, și apoi, de asemenea, să fie modificate automat schemele, dar cum structura se schimbă extrem de rar, ultima dată a fost în 2016, e foarte leneș să faci un astfel de automat. Mai bine să pierzi 30 de secunde pentru CTRL+C – CTRL+V.

De aceea, încărcarea în tabelul cu scheme este atât de hardcodată, iar căile către fișierele care trebuie încărcate, precum și numele tabelelor, sunt scrise manual.

A doua interogare – extrage informații despre structura tabelelor din scheme. Nu m-am complicat cu 3NF, ci am extins-o ca un singur tabel.

De altfel, acordați atenție acestui mic detaliu (+)

Destul de des, începătorii pun întrebarea: este posibil să calculăm o expresie complexă într-o interogare o dată, iar apoi să o folosim de mai multe ori în mai multe locuri. Da, este posibil. Iată cum, de exemplu:

from dbo.[_FIAS_SCHEMAS] a
	Cross apply a.x.nodes('\\xs:attribute') t(n)
	Cross apply 
			(values -- unele câmpuri sunt folosite de mai multe ori, așa că să facem așa :) 
			(
			 x.value('(xs:schema[1]\/xs:element[1]\/@name)', 'nvarchar(255)')
, ... aici putem enumera toate câmpurile complicate pentru a le folosi apoi în orice parte a interogării
			)
			) tbl([root_name], [root_description], [name], [column], [required], [type], [len], [description])

Funcționează cu unele limitări, desigur. Dar, odată ce începeți să folosiți, veți înțelege rapid care sunt acestea.

3. Vom crea scripturi care creează tabelele FIAS, iar apoi le vom rula și vom crea tabelele propriu-zise:

Declarați @schema sysname = N'dbo'
Declarați @sql nvarchar(max)

-- Vom crea codul pentru a crea tabele, și îl vom executa
set @sql = (
	Select Distinct 
		N';Crează tabel ' 
		+ Quotename(@schema)
		+ N'.'
		+ Quotename([table “” not found \/]

)
		+ N'('
		+ Stuff((Select
				N',' 
				+ QUOTENAME(b.[column]) 
				+ N' '
				+ b.[sqltype]
				+ Caz b.[required] când 'required' atunci N' NOT NULL'
									când 'optional' atunci N' NULL'
					End 
				From dbo.[_FIAS] b
				where b.[table “” not found \/]

 = a.[table “” not found \/]


				Order by N ASC
				Pentru xml path(N''), tip
				).value(N'.', 'nvarchar(max)'), 1, 1, N'')
		+ N')'
		from dbo.[_FIAS] a
	Pentru xml path(N''), tip).value(N'.', N'nvarchar(max)')
exec (@sql)

Celor care le este lene să facă toate acestea – scripturile pentru crearea tabelelor și schemele FIAS annotate vor fi anexate la sfârșitul articolului.

Interogarea pentru crearea tabelelor, care a rezultat în urma executării interogării

;

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. Deoarece în xsd există o descriere corectă a tabelilor și a câmpurilor din acestea – o vom utiliza și vom crea o descriere a structurii în proprietățile extinse ale tabelilor și ale câmpurilor lor:

-- Vom crea o notă pentru tabele și câmpuri
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 “” not found \/]


		+ N''''
from (Select distinct [table “” not found \/]

, [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 “” not found \/]

 
		+ 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)

Iată codul care a rezultat și s-a executat în urma muncii:

;

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. Modificăm schemele:

-- Definirea legăturii către schema importată
update a
Set x.modify ('declare namespace xs="http://www.w3.org/2001/XMLSchema";             
insert   as first
into (xs:schema)[1]')
from dbo.[_FIAS_SCHEMAS] a

-- Definirea atributului rădăcină
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

-- Definirea nodului care este un rând de tabel și tabelul asociat
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

-- Anotarea câmpurilor în schemă
-- Deoarece se poate modifica doar un nod la un moment dat - va trebui să utilizăm un cursor
Declare @table nvarchar(4000)
Declare @column nvarchar(4000)

Declare cur CURSOR LOCAL FORWARD_ONLY
	for
	Select [table “” not found \/]

, [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 “” not found \/]

 = b.[table “” not found \/]


	  cross apply (Values(QUOTENAME(b.[column]))) t([column]) 
	  Where b.[table “” not found \/]

 = @table and b.[column] = @column

FETCH NEXT FROM cur into @table, @column
END
CLOSE cur;  
DEALLOCATE cur;  

6. Și, în final, vom exporta schemele xsd pe disc pentru utilizare ulterioară:

-- Exportare scheme
-- Vă rugăm să rețineți, calea către schemele exportate este hardcodată!
Set @sql = (
Select N'; exec xp_cmdshell ''bcp "Select [x] from [FIAS2].[dbo].[_FIAS_SCHEMAS] where [table “” not found \/]

 = ''''' + a.[table “” not found \/]

 + '''''" queryout "c:filesFIAS' + a.[table “” not found \/]

 + '.xsd" -S . -T -w'''
from dbo.[_FIAS_SCHEMAS] a
for xml path(N''), type
).value(N'.', N'nvarchar(max)')

exec (@sql)

Totul! :-)))

Iată întreaga cerere care generează tabelele, descrierile lor, modifică schemele și le exportă pe disc:

FOLOSIȚI [FIAS2]
goa

 dacă OBJECT_ID('dbo.[_FIAS]') nu este null 
	drop table dbo.[_FIAS]
goa

 dacă OBJECT_ID('dbo._FIAS_SCHEMAS') nu este null 
	drop table dbo.[_FIAS_SCHEMAS]
goa

Declară @schema sysname = N'dbo'
Declară @sql nvarchar(max)

Creează tabel dbo.[_FIAS_SCHEMAS] (x xml, [table “” not found \/]

 sysname)

inserează în dbo.[_FIAS_SCHEMAS] (x, [table “” not found \/]

)
SELECT x = CAST(BulkColumn AS XML).query('.'), '_ACTSTAT' [table "" not found \/]

 DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_ACTSTAT_2_250_08_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_ADDROBJ' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_ADDROBJ_2_250_01_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_CENTERST' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_CENTERST_2_250_10_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_CURENTST' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_CURENTST_2_250_07_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_ADDROBJ' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_ADDROBJ_2_250_15_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_HOUSEINT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_HOUSEINT_2_250_17_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_HOUSE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_HOUSE_2_250_16_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_LANDMARK' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_LANDMARK_2_250_18_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_DEL_NORMDOC' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_NORMDOC_2_250_19_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_ESTSTAT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_ESTSTAT_2_250_13_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_FLATTYPE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_FLATTYPE_2_250_23_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_HOUSEINT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_HOUSEINT_2_250_03_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_HOUSE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_HOUSE_2_250_02_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_HSTSTAT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_HSTSTAT_2_250_12_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_INTVSTAT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_INTVSTAT_2_250_11_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_LANDMARK' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_LANDMARK_2_250_04_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_NDOCTYPE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_NDOCTYPE_2_250_20_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_NORMDOC' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_NORMDOC_2_250_05_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_OPERSTAT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_OPERSTAT_2_250_09_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_ROOMTYPE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_ROOMTYPE_2_250_24_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_ROOM' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_ROOM_2_250_21_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_SOCRBASE' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_SOCRBASE_2_250_06_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_STEAD' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_STEAD_2_250_22_04_01_01.xsd', SINGLE_BLOB) x Union all
SELECT x = CAST(BulkColumn AS XML).query('.'), '_STRSTAT' DIN OPENROWSET(BULK 's01sq03c$filesFIASAS_STRSTAT_2_250_14_04_01_01.xsd', SINGLE_BLOB) x 


;CU WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema' as xs) 
Select a.[table “” not found \/]


  ,tbl.[root_name]
  ,tbl.[root_description]
  ,tbl.[name]
  ,ROW_NUMBER() over (partition by a.[table “” not found

 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]
  ,t.n.query('.') [node]
into dbo.[_FIAS]
from dbo.[_FIAS_SCHEMAS] a
	Cross apply a.x.nodes('//xs:attribute') t(n)
	Cross apply 
			(values -- unele câmpuri sunt utilizate de mai multe ori, așa că să facem așa :) 
			(
				 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)')
				,t.n.value('(.//@name)[1]', 'nvarchar(255)')
				,t.n.value('(.//@use)[1]', 'nvarchar(255)') 
				,Stuff(Coalesce(t.n.value('(.//xs:simpleType/xs:restriction/@base)[1]', 'nvarchar(255)'), t.n.value('(.//@type)[1]', 'nvarchar(255)')), 1, 3, '')
				,Coalesce(t.n.value('(.//xs:simpleType/xs:restriction/xs:length/@value)[1]', 'int')
							,t.n.value('(.//xs:simpleType/xs:restriction/xs:maxLength/@value)[1]', 'int')
							,t.n.value('(.//xs:simpleType/xs:restriction/xs:totalDigits/@value)[1]', 'int'))
				,Replace(
				 Replace(
				 Replace(
				 Replace(
				 Replace(
				 Replace(
				 Replace(
				 Replace(
				 Replace(t.n.value('(.//xs:annotation/xs:documentation)[1]', 'nvarchar(4000)'), '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ')
				 , '  ', ' ') 
			)
			) tbl([root_name], [root_description], [name], [column], [required], [type], [len], [description])

-- Să vedem rezultatul
Select * from dbo.[_FIAS]

-- Să creăm cod pentru a crea tabelele și să-l rulăm
set @sql = (
	Select Distinct 
		N';Create table ' 
		+ Quotename(@schema)
		+ N'.'
		+ Quotename([table "" not found \/]

)
		+ N'('
		+ Stuff((Select
				N',' 
				+ QUOTENAME(b.[column]) 
				+ N' '
				+ b.[sqltype]
				+ Caz b.[required] când 'required' atunci N' NOT NULL'
									când 'optional' atunci N' NULL'
					End 
				From dbo.[_FIAS] b
				where b.[table “” not found \/]

 = a.[table “” not found \/]


				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)

-- Să creăm o notă pentru tabele și câmpuri
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 "" not found \/]


		+ N''''
from (Select distinct [table “” not found \/]

, [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 “” not found \/]

 
		+ 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)

-- Definirea legăturii către schema importată
update a
Set x.modify ('declare namespace xs="http://www.w3.org/2001/XMLSchema";             
insert   as first
into (xs:schema)[1]')
from dbo.[_FIAS_SCHEMAS] a

-- Definirea atributului rădăcină
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

-- Definirea nodului care reprezintă o linie de tabel și tabelul asociat
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

-- Anotarea câmpurilor în schemă
-- Deoarece se poate modifica o singură nodă deodată, va trebui să folosim un cursor
Declare @table nvarchar(4000)
Declare @column nvarchar(4000)

Declare cur CURSOR LOCAL FORWARD_ONLY
	for
	Select [table "" not found \/]

, [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 “” not found \/]

 = b.[table “” not found \/]


	  cross apply (Values(QUOTENAME(b.[column]))) t([column]) 
	  Where b.[table “” not found \/]

 = @table and b.[column] = @column

FETCH NEXT FROM cur into @table, @column
END
CLOSE cur;  
DEALLOCATE cur;  

-- Exportarea schemelor
-- Rețineți că calea către schemele exportate este hardcodată!
Set @sql = (
Select N'; exec xp_cmdshell ''bcp "Select [x] from [FIAS2].[dbo].[_FIAS_SCHEMAS] where [table "" not found \/]

 = ''''' + a.[table “” not found \/]

 + '''''" queryout "c:filesFIAS' + a.[table “” not found \/]

 + '.xsd" -S . -T -w'''
from dbo.[_FIAS_SCHEMAS] a
for xml path(N''), type
).value(N'.', N'nvarchar(max)')

exec (@sql)

Acum mai rămâne doar să apelăm procedura de încărcare pentru fiecare fișier xml cu schema corespunzătoare (fișierele despachetate sunt stocate în folderul e:tmp, iar schemele – le-am exportat în c:filesFIAS):

UTILIZAȚI FIAS2
MERGEȚI

Declarați @sql nvarchar(max)

dacă OBJECT_ID('tempdb..#t') nu este nul
	drop table #t

Creați tabel #t ([output] sysname NULL)

inserați în #t
exec xp_cmdshell 'dir e:tmp \/B'

;Cu load_data ca
(
Select 
'_' + t.x.value('(a[2])', 'nvarchar(255)') [table “” not found 
]


,t.x.value('(a[3])', 'date') [fias_date]
,t.x.value('(a[4])', 'uniqueidentifier') [fias_guid]
,a.[output] [file_name]
	from #t a
	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:filesFIAS' + b.[table “” not found 
]

 + '.xsd'', ''FIAS2'', ''e:tmperr.xml''' 
 from load_data a
		inner join [dbo].[_FIAS_SCHEMAS] b on a.[table “” not found 
]

 = b.[table “” not found \/]


Pentru xml path(''), tip).value(N'.', N'nvarchar(max)')

Exec (@sql)

Uf…

Ei bine, acum în sfârșit totul este gata.

Totuși, iată ce vreau să vă spun, stimați colegi!
Totul funcționează pur și simplu extrem de lent!
Nu mă refer la crearea tabelelor și a schemelor, desigur. Ci la încărcare.

În mediu de testare, încărcarea a durat aproape 5 ore (sic!!!). Și asta fără indexarea tabelelor.

Între timp, încărcarea sistemului de intrare-ieșire a fost la nivelul de 3 MB/s, și au fost folosite doar câteva nuclee.

Încărcarea obiectelor adresabile s-a făcut cu o viteză de maximum 3 mii de înregistrări pe secundă, iar încărcarea clădirilor – cu o viteză de maximum 8 mii pe secundă. Iar acestea, pentru a spune doar atât, sunt 60 de milioane.
Din ce este cauzată această situație – nu pot spune, va trebui să mai investighez codul și, se pare, și setările VM.

Rețineți că încărcarea se face în loturi. Iar apoi se presupune că vor fi create indecși clusterați pe diferitele lor AOID.

Deoarece acestea reprezintă GUID-uri, cel mai bine este să procedăm astfel, pentru că, dacă se creează indecși clusterați ÎNAINTE de încărcare – va rezulta o tabelă fragmentată cu aproximativ 50%, nu mai puțin. Ei bine, și indecșii este mai bine să-i creăm după încărcare, deoarece inserția în indecși încetinește și mai mult încărcarea.

De aceea, numele tabelelor mele începe cu un underscore (_). Se presupunea că mai întâi voi încărca datele în ele, apoi voi recrea indecșii tabelelor vizate, și, apoi, voi face liniștit un Alter table switch.

De asemenea, rețineți că tipurile de date pentru câmpurile tabelelor FIAS nu sunt deloc optimizate.
De exemplu, utilizarea int, acolo unde este suficient tinyint, sau nvarchar în câmpuri unde se preconizează stocarea doar a valorilor numerice, cum ar fi codul regiunii sau OKATO.
Asta în scheme :-(

Totuși, nimic nu vă împiedică, după ce ați creat tabelul de metadate, [dbo].[_FIAS], să vă jucați cu el manual și să faceți ajustări, iar apoi să creați noi scheme de încărcare pe baza metadatelor corectate.

Ei bine, dacă nu reușiți să eliminați această încărcare lentă, va trebui, probabil, să reveniți la varianta de încărcare din dbf și să deschideți, în sfârșit, krakozyabri în micile dbf. Și să scrieți încă un articol.

dbf s-a încărcat normal în 8 fire în aproximativ 1 oră, iar alte aproximativ o oră a fost consumată pentru a fuziona tabelele separate în cele țintă și pentru a construi indicele pe ele.

… Sau, în sfârșit, să vă scoateți mâinile din jjj… eee… afedron, și să faceți, în sfârșit, o aplicație de tip data tier normală, și să încărcați deja datele, ca toți oamenii normali, prin SSIS.

Dar aceasta este o poveste cu totul diferită.

→ Scripturi și scheme xsd

Linkuri relevante:

→ FIAS

Imaginea cu cățelul - nu este a mea 🙂

Sursa: habr.com