Epigrafe:
«Quando hai un martello in mano, tutto intorno sembra chiodi».
Un tempo, sembra – venerdì scorso, mentre esploravo i dintorni dell'ufficio, la maledetta direzione si è preoccupata del fatto che passassi il tempo oziando e osservando gattini e gatte.
— Non ti piacerebbe caricare il FIAS, amico? – disse la direzione. – Poiché il processo di caricamento non sembra piacere alle nostre unità aziendali. Dicono che impiega tempo, carica il server dei prodotti, e inoltre il tipo che ha scritto il processo di caricamento è stato licenziato da poco, circa tre anni fa.
Inoltre, tutto lì deve essere modificato, quindi prendi, crea il tuo database e assicurati un caricamento periodico del FIAS. Insomma, non ti tratteniamo!
A questo punto va detto che ho un rapporto distante con la programmazione, in quanto sono più un DBA. Anche se, d'altra parte, il caricamento di grandi volumi di dati pre-trasformati è proprio lavoro da DBA, giusto?
— Va bene... Lo faremo subito – dissi alla direzione, e mi precipitai sul sito del FIAS, arrotolando le maniche.
«Oh! Qui ci sono dei dbf!» — pensai, strofinandomi le mani con gioia, mentre mi meravigliavo dell'assenza di un «de facto» zip standard, e, al contrario, della presenza di un arj ormai defunto e del proprietario pardon, aperto, ovviamente, 7zip [ma che comunque non può essere estratto usando powershell Expand-Archive]. Cioè, con puro powershell non lo scaricherai né lo estrarrai. Dovrò caricare sul server un sacco di robaccia. Ma va bene.
Ho scritto un tool per il caricamento massivo parallelo di file dbf da diversi anni, quindi non dovrebbero presentarsi problemi.
Ho estratto i dbf, avviato il programma di caricamento e, mentre i dati venivano caricati, ho buttato giù uno script che combinava le singole tabelle "quasi con lo stesso nome" in una sola, a seconda della loro appartenenza.
Ho caricato i dati e stavo già per andare nell'ufficio dirigenziale a raccogliere i meriti, ma il diavolo mi ha spinto a controllare i risultati dell'importazione!
— Ops. WTF!!!???
Le tabelle grandi sono state caricate correttamente, mentre quelle piccole contenevano dei caratteri strani.
E così mi sono sentito così triste e abbattuto da tutto ciò che con coraggio ho preso me stesso e mi sono dedicato alla procrastinazione e ai miei compiti diretti. Non avevo proprio voglia di lavorare con i dbf danneggiati.
Ho procrastinato per un paio di giorni, finché le domande non sono terminate e la dirigenza non è riapparsa all'orizzonte, con la fatidica domanda «Cosa stiamo facendo qui, ci stiamo rilassando?».
E, poiché non c'era nulla da rispondere, e non avevo voglia di occuparmi del dbf – ho deciso di caricare il FIAS da xml, soprattutto perché, come si dice, stiloso, alla moda, per i giovani, e «dbf è un formato morente».
Con questo, vi prego di consentirmi di terminare questo prolisso monologo introduttivo e passare al sodo.
Epigrafe 2:
Sì, si può fare anche così. 🙂
Quindi, è stato deciso di caricare utilizzando – una straordinaria libreria progettata appositamente per il caricamento massivo (bulk) di file xml strutturati.
Per poterla utilizzare, è necessario installare anche la libreria SqlXml 4.0 Service Pack 1 (SP1).
Tuttavia, per quanto riguarda il FIAS, la «strutturalità» non è particolarmente richiesta. Poiché i file non sono esattamente xml… sono sicuramente xml, ma in sostanza – sono tabelle piatte con dati, in ogni file c'è una tabella.
Sul sito sql.ru ho la procedura spXMLBulkLoad dell'illustre utente Mnior, per non uscire del tutto dai confini del server SQL e non scrivere nemmeno la chiamata a SQLXMLBULKLOAD su CLR.
Ecco una versione leggermente modificata:
spXMLBulkLoad
USARE [FIAS2]
GO
/****** Oggetto: StoredProcedure [dbo].[spXMLBulkLoad] Data Script: 13.05.2019 18:05:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- È necessario abilitare le procedure di automazione Ole:
-- EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;
-- Esempio:
-- 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_OASetProperty @OLEXMLBulk, 'ForceTableLock', 1
-- IF (@ErrCode 0)
-- BEGIN SET @ErrMethod = 'ForceTableLock'
-- GOTO Error
-- END
EXEC @ErrCode = sys.sp_OAMethod @OLEXMLBulk, 'Execute', NULL, @Schema, @File
IF (@ErrCode 0)
BEGIN
SET @ErrMethod = 'Execute'
DECLARE @Exist Int
,@Error XML
,@SQL NvarChar(max)
EXEC master.dbo.xp_FileExist @ErrorLog, @Exist OUT
IF (@Exist = 1)
BEGIN
-- Dal file @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 = 'Errore nella creazione dell'oggetto OLE'
-- Visualizzazione degli errori
IF (@ErrMethod IS NOT NULL)
BEGIN
RAISERROR('Errore durante l''esecuzione del metodo "%s" in "%s": %s',18,1, @ErrMethod, @ErrorLog, @ErrDescript)
RETURN @@Error
END
END
GO
Tuttavia, per effettuare il caricamento massivo di xml utilizzando questa libreria, sono necessari schemi xsd annotati, che indicano, di fatto, come e dove devono essere caricati i dati.
Ho trovato riferimenti a schemi di questo tipo, "ma solo vecchi", su almeno dieci siti, ma non ho trovato da nessuna parte i schemi effettivi. E mi sono arrabbiato.
Modificare lo schema attuale sul sito FIAS per importare i dati manualmente non è difficile.
Ma... nel complesso, ci sono 271 campi! Quante ore di lavoro ci vorranno!
Quindi ho deciso di modificare automaticamente questi schemi, creando anche tabelle di destinazione nel database.
SQLXMLBULKLOAD può creare automaticamente tabelle per i dati da caricare dallo schema annotato, ma, d'altra parte, se devo creare questo schema, perché non fare queste tabelle da solo, come voglio?
Ho scaricato gli schemi xsd dal sito FIAS e li ho analizzati puramente visivamente.
Fortunatamente, sono tutti dello stesso tipo, quindi è possibile creare un database di destinazione e modificare gli schemi per il caricamento con solo alcune query non molto complesse.
1. Creiamo un database vuoto FIAS2.
Perché «2»? Beh, perché «1» era il database da dbf. Forse ne parleremo più tardi.
Script di creazione del database 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. Creiamo un paio di tabelle in questo database.
La prima tabella conterrà gli schemi xsd, mentre la seconda rappresenterà effettivamente lo schema dei dati ottenuto da questi schemi xsd.
Gli schemi xsd sono, in effetti, file xml normali, quindi è possibile lavorarci come con normali xml.
USA [FIAS2]
go
se OBJECT_ID('dbo.[_FIAS]') non è nullo
drop table dbo.[_FIAS]
go
se OBJECT_ID('dbo._FIAS_SCHEMAS') non è nullo
drop table dbo.[_FIAS_SCHEMAS]
go
Crea tabella dbo.[_FIAS_SCHEMAS] (x xml, [table “” not found \/]
sysname)
go
-- Prima query: Carichiamo gli schemi nella tabella sul server, per ulteriori manipolazioni.
insert into dbo.[_FIAS_SCHEMAS] (x, [table “” not found \/]
)
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_ACTSTAT' [table “” not found \/]
FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM 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' FROM OPENROWSET(BULK 's01sq03c$filesFIASAS_STRSTAT_2_250_14_04_01_01.xsd', SINGLE_BLOB) x
-- Second query: We will decompose the schemas and create a working table with metadata
;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 -- alcuni campi vengono utilizzati più volte, quindi faremo così :)
(
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])
-- Vediamo il risultato
Select * from dbo.[_FIAS]
Ulteriori dettagli sulle query:
La prima – carica gli schemi xsd in una tabella sul server, fornendo a ogni schema un'annotazione nel campo [table “” not found \/]
– con il nome della tabella in cui voglio caricare i dati dalla tabella corrispondente.
Senza dubbio, sarebbe stato possibile fare in modo che ogni volta che gli schemi cambiavano, potessero essere scaricati, messi in una cartella e la struttura delle tabelle target ricreata di nuovo e automaticamente, e di nuovo automaticamente modificare gli schemi. Tuttavia, poiché la struttura cambia molto raramente, l'ultima volta nel 2016, è molto pigro creare un tale automatismo. È meglio investire 30 secondi in CTRL+C – CTRL+V.
Pertanto, il caricamento nel foglio con gli schemi è così hardcoded, e i percorsi ai file caricati, così come i nomi delle tabelle, sono scritti a mano.
La seconda query estrae dalle schemi informazioni sulla struttura delle tabelle. Non mi sono impegnato in 3NF, l'ho sviluppata come un'unica tabella.
Attirate, peraltro, l'attenzione su questo pezzo (+)
Di frequente i principianti pongono la domanda: è possibile calcolare un'espressione complessa in una query 1 volta e poi utilizzarla più volte in diversi punti. Sì, è possibile. Ecco come, ad esempio:
from dbo.[_FIAS_SCHEMAS] a
Cross apply a.x.nodes('\\xs:attribute') t(n)
Cross apply
(values -- alcuni campi sono utilizzati più volte, quindi faremo così :)
(
x.value('(xs:schema[1]/xs:element[1]/@name)', 'nvarchar(255)')
, ... qui è possibile elencare tutti i campi complessi da calcolare, per poi utilizzarli in qualsiasi parte della query
)
) tbl([root_name], [root_description], [name], [column], [required], [type], [len], [description])
Funziona con alcune limitazioni, ovviamente. Ma, iniziando a usarlo, capirai rapidamente quali siano.
3. Creeremo script per creare le tabelle FIAS, poi li eseguiremo e creeremo le tabelle stesse:
Dichiarare @schema sysname = N'dbo'
Dichiarare @sql nvarchar(max)
-- Creiamo il codice per la creazione delle tabelle ed eseguiamolo
set @sql = (
Seleziona Distinct
N';Creare tabella '
+ Quotename(@schema)
+ N'.'
+ Quotename([table “” not found \/]
)
+ N'('
+ Stuff((Seleziona
N','
+ QUOTENAME(b.[column])
+ N' '
+ b.[sqltype]
+ Caso b.[required] quando 'required' allora N' NOT NULL'
quando 'optional' allora N' NULL'
Fine
Da dbo.[_FIAS] b
dove b.[table “” not found \/]
= a.[table “” not found \/]
Ordina per N ASC
Per xml path(N''), tipo
).value(N'.', 'nvarchar(max)'), 1, 1, N'')
+ N')'
da dbo.[_FIAS] a
Per xml path(N''), tipo).value(N'.', N'nvarchar(max)')
exec (@sql)
A chi è pigro di fare tutto questo – gli script per la creazione delle tabelle e gli schemi annotati FIAS saranno allegati alla fine dell'articolo.
Query per la creazione delle tabelle, che è risultata dal lavoro della query
;
CREA TABELLA [dbo].[_ACTSTAT] (
[NAME] NVARCHAR(100) NOT NULL
,[ACTSTATID] BIGINT NOT NULL
);
CREA TABELLA [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] DATA 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] DATA NOT NULL
,[ENDDATE] DATA NOT NULL
,[NORMDOC] UNIQUEIDENTIFIER NULL
,[LIVESTATUS] TINYINT NOT NULL
,[DIVTYPE] INT NOT NULL
,[OKTMO] NVARCHAR(11) NULL
);
CREA TABELLA [dbo].[_CENTERST] (
[CENTERSTID] BIGINT NOT NULL
,[NAME] NVARCHAR(100) NOT NULL
);
CREA TABELLA [dbo].[_CURENTST] (
[CURENTSTID] BIGINT NOT NULL
,[NAME] NVARCHAR(100) NOT NULL
);
CREA TABELLA [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] DATA NOT NULL
,[ENDDATE] DATA 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] DATA NOT NULL
);
CREA TABELLA [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] DATA 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] DATA NOT NULL
,[ENDDATE] DATA NOT NULL
,[STATSTATUS] BIGINT NOT NULL
,[NORMDOC] UNIQUEIDENTIFIER NULL
,[COUNTER] BIGINT NOT NULL
,[CADNUM] NVARCHAR(100) NULL
,[DIVTYPE] INT NOT NULL
);
CREA TABELLA [dbo].[_DEL_HOUSEINT] (
[IFNSUL] NVARCHAR(4) NULL
,[TERRIFNSUL] NVARCHAR(4) NULL
,[OKATO] NVARCHAR(11) NULL
,[OKTMO] NVARCHAR(11) NULL
,[UPDATEDATE] DATA 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] DATA NOT NULL
,[ENDDATE] DATA NOT NULL
,[INTSTATUS] BIGINT NOT NULL
,[NORMDOC] UNIQUEIDENTIFIER NULL
,[COUNTER] BIGINT NOT NULL
);
CREA TABELLA [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] DATA NOT NULL
,[LANDID] UNIQUEIDENTIFIER NOT NULL
,[LANDGUID] UNIQUEIDENTIFIER NOT NULL
,[AOGUID] UNIQUEIDENTIFIER NOT NULL
,[STARTDATE] DATA NOT NULL
,[ENDDATE] DATA NOT NULL
,[NORMDOC] UNIQUEIDENTIFIER NULL
);
CREA TABELLA [dbo].[_DEL_NORMDOC] (
[NORMDOCID] UNIQUEIDENTIFIER NOT NULL
,[DOCNAME] NVARCHAR(MAX) NULL
,[DOCDATE] DATA NULL
,[DOCNUM] NVARCHAR(20) NULL
,[DOCTYPE] BIGINT NOT NULL
,[DOCIMGID] UNIQUEIDENTIFIER NULL
);
CREA TABELLA [dbo].[_ESTSTAT] (
[ESTSTATID] BIGINT NOT NULL
,[NAME] NVARCHAR(20) NOT NULL
,[SHORTNAME] NVARCHAR(20) NULL
);
CREA TABELLA [dbo].[_FLATTYPE] (
[SHORTNAME] NVARCHAR(20) NULL
,[FLTYPEID] BIGINT NOT NULL
,[NAME] NVARCHAR(20) NOT NULL
);
CREA TABELLA [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] DATA 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] DATA NOT NULL
,[ENDDATE] DATA NOT NULL
,[STATSTATUS] BIGINT NOT NULL
,[NORMDOC] UNIQUEIDENTIFIER NULL
,[COUNTER] BIGINT NOT NULL
,[CADNUM] NVARCHAR(100) NULL
,[DIVTYPE] INT NOT NULL
);
CREA TABELLA [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] DATA NOT NULL
,[INTSTATUS] BIGINT NOT NULL
,[AOGUID] UNIQUEIDENTIFIER NOT NULL
,[STARTDATE] DATA NOT NULL
,[INTGUID] UNIQUEIDENTIFIER NOT NULL
,[HOUSEINTID] UNIQUEIDENTIFIER NOT NULL
,[INTSTART] BIGINT NOT NULL
,[INTEND] BIGINT NOT NULL
,[UPDATEDATE] DATA NOT NULL
,[NORMDOC] UNIQUEIDENTIFIER NULL
,[COUNTER] BIGINT NOT NULL
);
CREA TABELLA [dbo].[_HSTSTAT] (
[HOUSESTID] BIGINT NOT NULL
,[NAME] NVARCHAR(60) NOT NULL
);
CREA TABELLA [dbo].[_INTVSTAT] (
[INTVSTATID] BIGINT NOT NULL
,[NAME] NVARCHAR(60) NOT NULL
);
CREA TABELLA [dbo].[_LANDMARK] (
[NORMDOC] UNIQUEIDENTIFIER NULL
,[STARTDATE] DATA NOT NULL
,[ENDDATE] DATA 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] DATA NOT NULL
,[OKTMO] NVARCHAR(11) NULL
,[OKATO] NVARCHAR(11) NULL
,[IFNSUL] NVARCHAR(4) NULL
,[TERRIFNSUL] NVARCHAR(4) NULL
);
CREA TABELLA [dbo].[_NDOCTYPE] (
[NDTYPEID] BIGINT NOT NULL
,[NAME] NVARCHAR(250) NOT NULL
);
CREA TABELLA [dbo].[_NORMDOC] (
[NORMDOCID] UNIQUEIDENTIFIER NOT NULL
,[DOCNAME] NVARCHAR(MAX) NULL
,[DOCDATE] DATA NULL
,[DOCNUM] NVARCHAR(20) NULL
,[DOCTYPE] BIGINT NOT NULL
,[DOCIMGID] UNIQUEIDENTIFIER NULL
);
CREA TABELLA [dbo].[_OPERSTAT] (
[OPERSTATID] BIGINT NOT NULL
,[NAME] NVARCHAR(100) NOT NULL
);
CREA TABELLA [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] DATA NOT NULL
,[HOUSEGUID] UNIQUEIDENTIFIER NOT NULL
,[ROOMID] UNIQUEIDENTIFIER NOT NULL
,[PREVID] UNIQUEIDENTIFIER NULL
,[NEXTID] UNIQUEIDENTIFIER NULL
,[STARTDATE] DATA NOT NULL
,[ENDDATE] DATA NOT NULL
,[LIVESTATUS] TINYINT NOT NULL
,[NORMDOC] UNIQUEIDENTIFIER NULL
,[OPERSTATUS] BIGINT NOT NULL
,[CADNUM] NVARCHAR(100) NULL
,[ROOMCADNUM] NVARCHAR(100) NULL
);
CREA TABELLA [dbo].[_ROOMTYPE] (
[SHORTNAME] NVARCHAR(20) NULL
,[NAME] NVARCHAR(20) NOT NULL
,[RMTYPEID] BIGINT NOT NULL
);
CREA TABELLA [dbo].[_SOCRBASE] (
[LEVEL] BIGINT NOT NULL
,[SCNAME] NVARCHAR(10) NULL
,[SOCRNAME] NVARCHAR(50) NOT NULL
,[KOD_T_ST] NVARCHAR(4) NOT NULL
);
CREA TABELLA [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] DATA NOT NULL
,[PARENTGUID] UNIQUEIDENTIFIER NULL
,[STEADID] UNIQUEIDENTIFIER NOT NULL
,[PREVID] UNIQUEIDENTIFIER NULL
,[NEXTID] UNIQUEIDENTIFIER NULL
,[OPERSTATUS] BIGINT NOT NULL
,[STARTDATE] DATA NOT NULL
,[ENDDATE] DATA NOT NULL
,[NORMDOC] UNIQUEIDENTIFIER NULL
,[LIVESTATUS] TINYINT NOT NULL
,[CADNUM] NVARCHAR(100) NULL
,[DIVTYPE] INT NOT NULL
);
CREA TABELLA [dbo].[_STRSTAT] (
[NAME] NVARCHAR(20) NOT NULL
,[SHORTNAME] NVARCHAR(20) NULL
,[STRSTATID] BIGINT NOT NULL
)
4. Poiché nello xsd è presente una descrizione adeguata delle tabelle e dei loro campi, la utilizziamo e creiamo una descrizione della struttura nelle proprietà estese delle tabelle e dei loro campi:
-- Creiamo un'annotazione per tabelle e campi
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)
Ecco il codice che è stato generato e ha funzionato come risultato dell'elaborazione
;
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. Modifichiamo gli schemi stessi:
-- Definizione del link allo schema importato
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
-- Definizione dell'attributo radice
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
-- Definizione del nodo che è una riga della tabella e la tabella correlata
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
-- Annotazione dei campi nello schema
-- Poiché è possibile modificare solo un nodo per volta, sarà necessario utilizzare un cursore
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. E infine, estraiamo gli schemi xsd su disco per un utilizzo futuro:
-- Esportazione degli schemi
-- Si prega di notare che il percorso per gli schemi esportati è hardcoded!
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)
Tutto! :-)))
Ecco l'intera query che genera le tabelle, le loro descrizioni, modifica gli schemi e li estrae su disco:
UTILIZZA [FIAS2]
vai
se OBJECT_ID('dbo.[_FIAS]') non è nullo
droppa tabella dbo.[_FIAS]
vai
se OBJECT_ID('dbo._FIAS_SCHEMAS') non è nullo
droppa tabella dbo.[_FIAS_SCHEMAS]
vai
Dichiara @schema sysname = N'dbo'
Dichiara @sql nvarchar(max)
Crea tabella dbo.[_FIAS_SCHEMAS] (x xml, [table “” not found \/]
sysname)
inserisci in dbo.[_FIAS_SCHEMAS] (x, [table “” not found \/]
)
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_ACTSTAT' [table “” not found \/]
DA OPENROWSET(BULK 's01sq03c$filesFIASAS_ACTSTAT_2_250_08_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_ADDROBJ' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_ADDROBJ_2_250_01_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_CENTERST' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_CENTERST_2_250_10_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_CURENTST' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_CURENTST_2_250_07_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_DEL_ADDROBJ' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_ADDROBJ_2_250_15_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_DEL_HOUSEINT' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_HOUSEINT_2_250_17_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_DEL_HOUSE' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_HOUSE_2_250_16_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_DEL_LANDMARK' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_LANDMARK_2_250_18_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_DEL_NORMDOC' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_DEL_NORMDOC_2_250_19_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_ESTSTAT' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_ESTSTAT_2_250_13_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_FLATTYPE' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_FLATTYPE_2_250_23_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_HOUSEINT' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_HOUSEINT_2_250_03_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_HOUSE' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_HOUSE_2_250_02_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_HSTSTAT' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_HSTSTAT_2_250_12_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_INTVSTAT' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_INTVSTAT_2_250_11_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_LANDMARK' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_LANDMARK_2_250_04_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_NDOCTYPE' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_NDOCTYPE_2_250_20_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_NORMDOC' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_NORMDOC_2_250_05_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_OPERSTAT' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_OPERSTAT_2_250_09_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_ROOMTYPE' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_ROOMTYPE_2_250_24_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_ROOM' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_ROOM_2_250_21_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_SOCRBASE' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_SOCRBASE_2_250_06_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_STEAD' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_STEAD_2_250_22_04_01_01.xsd', SINGLE_BLOB) x Unione tutto
SELEZIONA x = CAST(BulkColumn AS XML).query('.'), '_STRSTAT' DA OPENROWSET(BULK 's01sq03c$filesFIASAS_STRSTAT_2_250_14_04_01_01.xsd', SINGLE_BLOB) x
WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema' come xs)
Seleziona a.[tabella "" non trovata /]
,tbl.[root_name]
,tbl.[root_description]
,tbl.[name]
,ROW_NUMBER() over (partition by a.[table “” not found \/]
ordine per 1/0) N
,tbl.[colonna]
,tbl.[richiesta]
,tbl.[descrizione]
,tbl.[tipo]
,tbl.[len]
,Case tbl.[tipo]
quando N'byte' allora N'tinyint'
quando N'date' allora N'date'
quando N'int' allora N'int'
quando N'string' allora
Case quando tbl.[len] = 36 e (tbl.[colonna] like N'%ID' oppure tbl.[colonna] = N'NORMDOC') allora N'uniqueidentifier'
quando tbl.[len] è Null allora N'nvarchar(max)'
Altro N'nvarchar(' + cast(tbl.[len] as nvarchar(4000)) + N')' FINE
quando N'integer' allora
Case quando tbl.[len] > 9 allora N'bigint'
quando tbl.[len] <= 4 allora N'smallint'
Altro N'int'
fine
altro N'nvarchar(max)'
FINE [sqltype]
,t.n.query('.') [nodo]
in dbo.[_FIAS]
da dbo.[_FIAS_SCHEMAS] a
Cross apply a.x.nodes('//@xs:attribute') t(n)
Cross apply
(valori -- alcuni campi vengono utilizzati più volte, quindi facciamo così :)
(
x.value('(xs:schema[1]xs:element[1]@name)', 'nvarchar(255)')
,x.value('(xs:schema[1]xs:element[1]xs:annotationxs: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:simpleTypexs:restriction@base)[1]', 'nvarchar(255)'), t.n.value('(.@type)[1]', 'nvarchar(255)')), 1, 3, '')
,Coalesce(t.n.value('(.xs:simpleTypexs:restrictionxs:length@value)[1]', 'int')
,t.n.value('(.xs:simpleTypexs:restrictionxs:maxLength@value)[1]', 'int')
,t.n.value('(.xs:simpleTypexs:restrictionxs:totalDigits@value)[1]', 'int'))
,Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(t.n.value('(.xs:annotationxs:documentation)[1]', 'nvarchar(4000)'), ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
)
) tbl([root_name], [root_description], [name], [column], [required], [type], [len], [description])
-- Vediamo il risultato
Select * from dbo.[_FIAS]
-- Creeremo il codice per la creazione delle tabelle e lo eseguiremo
set @sql = (
Select Distinct
N';Create table '
+ Quotename(@schema)
+ N'.'
+ Quotename([table “” not found \/]
)
+ N'('
+ Stuff((Seleziona
N','
+ QUOTENAME(b.[column])
+ N' '
+ b.[sqltype]
+ Caso b.[required] quando 'required' allora N' NOT NULL'
quando 'optional' allora N' NULL'
Fine
Da dbo.[_FIAS] b
dove b.[table “” not found \/]
= a.[table “” not found \/]
Ordina per N ASC
Per xml path(N''), tipo
).value(N'.', 'nvarchar(max)'), 1, 1, N'')
+ N')'
da dbo.[_FIAS] a
Per xml path(N''), tipo).value(N'.', N'nvarchar(max)')
exec (@sql)
-- Creiamo un'annotazione per le tabelle e i campi
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''Colonna'', @level2name = N'''
+ a.[column]
+ N''''
from dbo.[_FIAS] a
for xml path(N''), type
).value(N'.', N'nvarchar(max)')
exec (@sql)
-- Definizione del riferimento allo schema importato
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
-- Definizione dell'attributo radice
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
-- Definizione del nodo che rappresenta la riga della tabella e la tabella ad essa associata
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
-- Annotazione dei campi nello schema
-- Poiché è possibile modificare solo un nodo alla volta, sarà necessario utilizzare un cursore
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;
-- Esportazione degli schemi
-- Si prega di notare che il percorso per gli schemi esportati è hardcoded!
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)
Ora non resta che chiamare la procedura di caricamento per ogni file xml con il rispettivo schema (i file estratti sono memorizzati nella cartella e:tmp, mentre gli schemi li abbiamo estratti in c:filesFIAS):
USARE FIAS2
ANDARE
Dichiara @sql nvarchar(max)
se OBJECT_ID('tempdb..#t') non è nullo
cancella tabella #t
Crea tabella #t ([output] sysname NULL)
inserisci in #t
esegui xp_cmdshell 'dir e:tmp /B'
;Con load_data come
(
Seleziona
'_' + t.x.value('(a[2])', 'nvarchar(255)') [tabella “” non trovata ]
,t.x.value('(a[3])', 'date') [fias_date]
,t.x.value('(a[4])', 'uniqueidentifier') [fias_guid]
,a.[output] [nome_file]
da #t a
applica incrociato (VALORI(Cast('' + Sostituisci(Sostituisci(Sostituisci(Sostituisci(a.[output],'DEL_', 'DEL$'), '_', ''), '.', ''), '$', '_') + '' come xml))) t(x)
Dove [output] like 'AS[_]%.xml'
)
Seleziona @sql =
(Seleziona
N';esegui [dbo].[spXMLBulkLoad] ''e:tmp' + a.[nome_file] + ''', ''c:filesFIAS' + b.[tabella “” non trovata ]
+ '.xsd'', ''FIAS2'', ''e:tmperr.xml'''
da load_data a
unisciti a [dbo].[_FIAS_SCHEMAS] b su a.[tabella “” non trovata ]
Per xml percorso(''), tipo).value(N'.', N'nvarchar(max)')
Esegui (@sql)
= b.[table “” not found /]
Per xml path(''), type).value(N'.', N'nvarchar(max)')
Esegui (@sql)
Uff…
Ecco, adesso finalmente è tutto.
Tuttavia, voglio dirvi questo, cari colleghi!
Tutto ciò funziona semplicemente incredibilmente lento!
Non parlo della creazione di tabelle e schemi, ovviamente. Ma del caricamento.
Nell'ambiente di test, il caricamento è durato quasi 5 ore (sic!!!). E questo ancora senza indicizzazione delle tabelle.
Il caricamento del sistema di input/output era a un livello di 3 Mb/s, e sono stati utilizzati solo un paio di core.
Il caricamento degli oggetti indirizzo avveniva a una velocità non superiore a 3.000 record al secondo, mentre il caricamento degli edifici a una velocità non superiore a 8.000 al secondo. E ce ne sono, per dirla brevemente, 60 milioni.
Non so a cosa sia dovuto, dovrò scavare un po' nel codice e, apparentemente, nella configurazione della VM.
Fate attenzione, il caricamento viene effettuato in lotti. Solo dopo si prevede di creare indici cluster su vari AOID.
Poiché rappresentano GUID, è meglio fare esattamente così, perché se si creano indici cluster PRIMA del caricamento, alla fine si otterrà una tabella frammentata in percentuale del 50% o più. Inoltre, è meglio creare anche gli indici dopo il caricamento, perché l'inserimento negli indici rende il caricamento ancora più lento.
Proprio per questo, i nomi delle tabelle iniziano con un trattino basso (_). Si presumeva che prima caricassi i dati in esse, poi ricreassi gli indici delle tabelle destinate, e quindi facessi tranquillamente un Alter table switch.
Fate anche attenzione ai tipi di dati non del tutto ottimali per i campi delle tabelle FIAS.
Ad esempio, l'uso di int quando sarebbe sufficiente tinyint, o nvarchar nei campi dove si prevede di memorizzare solo valori numerici, come il codice della regione o l'OKATO.
Così nello schema :-(.
Tuttavia, non c'è niente che vi impedisca, dopo aver creato la tabella dei metadati, [dbo].[_FIAS], di smanettare con essa manualmente e fare il tuning, e poi creare nuovi schemi di caricamento basati sui metadati corretti.
E se non riuscite comunque a liberare questa lentezza nel caricamento, evidentemente dovrete tornare all'opzione del caricamento da dbf e finalmente decifrare le krakozyabras nei piccoli dbf. E scrivere un altro articolo.
Il dbf è stato caricato normalmente in 8 flussi in circa 1 ora e ci è voluto un altro oretta circa per unire le tabelle individuali in quelle finali e costruire gli indici su di esse.
… Oppure, infine, estrarre le mani da jjj… ehm… efedrone, e finalmente fare un'applicazione di livello dati normale, caricando i dati come fanno tutte le persone normali, tramite SSIS.
Ma questa è un'altra storia.
→
Riferimenti sull'argomento:
→
Immagine del cagnolino — non è mia 🙂
Fonte: habr.com
