johannes/jr-sql-ai
1
1
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
jr-sql-ai/query.sql
T

1590 lines
39 KiB
Transact-SQL
Raw Permalink Blame History

Here's a list of tables and their columns in a source database:
Database_Name Schema_Name Table_Name Column_Name
sysdb_UTF8 dbo c_StagingADContacts City
sysdb_UTF8 dbo c_StagingADContacts Company
sysdb_UTF8 dbo c_StagingADContacts Department
sysdb_UTF8 dbo c_StagingADContacts DepartmentNumber
sysdb_UTF8 dbo c_StagingADContacts Domain
sysdb_UTF8 dbo c_StagingADContacts FirstName
sysdb_UTF8 dbo c_StagingADContacts LastName
sysdb_UTF8 dbo c_StagingADContacts Mail
sysdb_UTF8 dbo c_StagingADContacts MobileNumber
sysdb_UTF8 dbo c_StagingADContacts PostalCode
sysdb_UTF8 dbo c_StagingADContacts StreetAddress
sysdb_UTF8 dbo c_StagingADContacts Team
sysdb_UTF8 dbo c_StagingADContacts TelephoneNumber
sysdb_UTF8 dbo c_StagingADContacts UserName
sysdb_UTF8 dbo c_StagingADGroupMembers Domain
sysdb_UTF8 dbo c_StagingADGroupMembers GroupName
sysdb_UTF8 dbo c_StagingADGroupMembers MemberDomain
sysdb_UTF8 dbo c_StagingADGroupMembers MemberName
sysdb_UTF8 dbo c_StagingADGroups Domain
sysdb_UTF8 dbo c_StagingADGroups GroupName
sysdb_UTF8 dbo c_StagingADServiceusers Description
sysdb_UTF8 dbo c_StagingADServiceusers Domain
sysdb_UTF8 dbo c_StagingADServiceusers UserName
sysdb_UTF8 dbo ComponentItem Accounting
sysdb_UTF8 dbo ComponentItem Active
sysdb_UTF8 dbo ComponentItem AdditionalValue1
sysdb_UTF8 dbo ComponentItem AdditionalValue2
sysdb_UTF8 dbo ComponentItem ChangeTime
sysdb_UTF8 dbo ComponentItem ChangeUser
sysdb_UTF8 dbo ComponentItem CreateTime
sysdb_UTF8 dbo ComponentItem CreateUser
sysdb_UTF8 dbo ComponentItem Description
sysdb_UTF8 dbo ComponentItem ExternalIdentifier
sysdb_UTF8 dbo ComponentItem FlexValue1
sysdb_UTF8 dbo ComponentItem FlexValue10
sysdb_UTF8 dbo ComponentItem FlexValue2
sysdb_UTF8 dbo ComponentItem FlexValue3
sysdb_UTF8 dbo ComponentItem FlexValue4
sysdb_UTF8 dbo ComponentItem FlexValue5
sysdb_UTF8 dbo ComponentItem FlexValue6
sysdb_UTF8 dbo ComponentItem FlexValue7
sysdb_UTF8 dbo ComponentItem FlexValue8
sysdb_UTF8 dbo ComponentItem FlexValue9
sysdb_UTF8 dbo ComponentItem ID
sysdb_UTF8 dbo ComponentItem Name
sysdb_UTF8 dbo ComponentItem SortIndex
sysdb_UTF8 dbo ComponentItem StatusID
sysdb_UTF8 dbo ComponentItem SyncSource
sysdb_UTF8 dbo ComponentItem SyncTime
sysdb_UTF8 dbo ComponentItem TypID
sysdb_UTF8 dbo ComponentItemContactRelation Active
sysdb_UTF8 dbo ComponentItemContactRelation ChangeTime
sysdb_UTF8 dbo ComponentItemContactRelation ChangeUser
sysdb_UTF8 dbo ComponentItemContactRelation ComponentItemID
sysdb_UTF8 dbo ComponentItemContactRelation ContactID
sysdb_UTF8 dbo ComponentItemContactRelation ContactTypID
sysdb_UTF8 dbo ComponentItemContactRelation CreateTime
sysdb_UTF8 dbo ComponentItemContactRelation CreateUser
sysdb_UTF8 dbo ComponentItemContactRelation Description
sysdb_UTF8 dbo ComponentItemContactRelation ID
sysdb_UTF8 dbo ComponentItemContactRelation Name
sysdb_UTF8 dbo ComponentItemContactRelation SortIndex
sysdb_UTF8 dbo ComponentItemLog Active
sysdb_UTF8 dbo ComponentItemLog ChangeTime
sysdb_UTF8 dbo ComponentItemLog ChangeUser
sysdb_UTF8 dbo ComponentItemLog ComponentItemID
sysdb_UTF8 dbo ComponentItemLog Content
sysdb_UTF8 dbo ComponentItemLog CreateTime
sysdb_UTF8 dbo ComponentItemLog CreateUser
sysdb_UTF8 dbo ComponentItemLog Description
sysdb_UTF8 dbo ComponentItemLog ID
sysdb_UTF8 dbo ComponentItemLog Name
sysdb_UTF8 dbo ComponentItemLog SortIndex
sysdb_UTF8 dbo ComponentItemRelation Active
sysdb_UTF8 dbo ComponentItemRelation ChangeTime
sysdb_UTF8 dbo ComponentItemRelation ChangeUser
sysdb_UTF8 dbo ComponentItemRelation ComponentItemAID
sysdb_UTF8 dbo ComponentItemRelation ComponentItemBID
sysdb_UTF8 dbo ComponentItemRelation CreateTime
sysdb_UTF8 dbo ComponentItemRelation CreateUser
sysdb_UTF8 dbo ComponentItemRelation Description
sysdb_UTF8 dbo ComponentItemRelation Direction
sysdb_UTF8 dbo ComponentItemRelation ID
sysdb_UTF8 dbo ComponentItemRelation Interface
sysdb_UTF8 dbo ComponentItemRelation Name
sysdb_UTF8 dbo ComponentItemRelation Port
sysdb_UTF8 dbo ComponentItemRelation Protocol
sysdb_UTF8 dbo ComponentItemRelation SortIndex
sysdb_UTF8 dbo ComponentItemRelation SyncSourceID
sysdb_UTF8 dbo ComponentItemRelation SyncTime
sysdb_UTF8 dbo ComponentItemRelation TypID
sysdb_UTF8 dbo ComponentItemRelationTyp Active
sysdb_UTF8 dbo ComponentItemRelationTyp ChangeTime
sysdb_UTF8 dbo ComponentItemRelationTyp ChangeUser
sysdb_UTF8 dbo ComponentItemRelationTyp CreateTime
sysdb_UTF8 dbo ComponentItemRelationTyp CreateUser
sysdb_UTF8 dbo ComponentItemRelationTyp DefaultDescription
sysdb_UTF8 dbo ComponentItemRelationTyp DefaultPort
sysdb_UTF8 dbo ComponentItemRelationTyp DefaultProtocol
sysdb_UTF8 dbo ComponentItemRelationTyp Description
sysdb_UTF8 dbo ComponentItemRelationTyp ID
sysdb_UTF8 dbo ComponentItemRelationTyp Name
sysdb_UTF8 dbo ComponentItemRelationTyp SortIndex
sysdb_UTF8 dbo ComponentItemTyp Active
sysdb_UTF8 dbo ComponentItemTyp AdditionalValueLabel1
sysdb_UTF8 dbo ComponentItemTyp AdditionalValueLabel2
sysdb_UTF8 dbo ComponentItemTyp AdditionalValueOption1
sysdb_UTF8 dbo ComponentItemTyp AdditionalValueOption2
sysdb_UTF8 dbo ComponentItemTyp ChangeTime
sysdb_UTF8 dbo ComponentItemTyp ChangeUser
sysdb_UTF8 dbo ComponentItemTyp CreateTime
sysdb_UTF8 dbo ComponentItemTyp CreateUser
sysdb_UTF8 dbo ComponentItemTyp Description
sysdb_UTF8 dbo ComponentItemTyp FlexValueLabel1
sysdb_UTF8 dbo ComponentItemTyp FlexValueLabel10
sysdb_UTF8 dbo ComponentItemTyp FlexValueLabel2
sysdb_UTF8 dbo ComponentItemTyp FlexValueLabel3
sysdb_UTF8 dbo ComponentItemTyp FlexValueLabel4
sysdb_UTF8 dbo ComponentItemTyp FlexValueLabel5
sysdb_UTF8 dbo ComponentItemTyp FlexValueLabel6
sysdb_UTF8 dbo ComponentItemTyp FlexValueLabel7
sysdb_UTF8 dbo ComponentItemTyp FlexValueLabel8
sysdb_UTF8 dbo ComponentItemTyp FlexValueLabel9
sysdb_UTF8 dbo ComponentItemTyp FlexValueOption1
sysdb_UTF8 dbo ComponentItemTyp FlexValueOption10
sysdb_UTF8 dbo ComponentItemTyp FlexValueOption2
sysdb_UTF8 dbo ComponentItemTyp FlexValueOption3
sysdb_UTF8 dbo ComponentItemTyp FlexValueOption4
sysdb_UTF8 dbo ComponentItemTyp FlexValueOption5
sysdb_UTF8 dbo ComponentItemTyp FlexValueOption6
sysdb_UTF8 dbo ComponentItemTyp FlexValueOption7
sysdb_UTF8 dbo ComponentItemTyp FlexValueOption8
sysdb_UTF8 dbo ComponentItemTyp FlexValueOption9
sysdb_UTF8 dbo ComponentItemTyp ID
sysdb_UTF8 dbo ComponentItemTyp ImageIndex
sysdb_UTF8 dbo ComponentItemTyp InterfaceAble
sysdb_UTF8 dbo ComponentItemTyp Name
sysdb_UTF8 dbo ComponentItemTyp NameDefault
sysdb_UTF8 dbo ComponentItemTyp NameLabel
sysdb_UTF8 dbo ComponentItemTyp SortIndex
sysdb_UTF8 dbo ComponentItemTyp SyncOnly
sysdb_UTF8 dbo Contact Active
sysdb_UTF8 dbo Contact Adress
sysdb_UTF8 dbo Contact ChangeTime
sysdb_UTF8 dbo Contact ChangeUser
sysdb_UTF8 dbo Contact Company
sysdb_UTF8 dbo Contact CreateTime
sysdb_UTF8 dbo Contact CreateUser
sysdb_UTF8 dbo Contact Description
sysdb_UTF8 dbo Contact Firstname
sysdb_UTF8 dbo Contact ID
sysdb_UTF8 dbo Contact Lastname
sysdb_UTF8 dbo Contact Mail
sysdb_UTF8 dbo Contact Mobile
sysdb_UTF8 dbo Contact Name
sysdb_UTF8 dbo Contact Phone
sysdb_UTF8 dbo Contact SortIndex
sysdb_UTF8 dbo Contact SyncSource
sysdb_UTF8 dbo Contact SyncTime
sysdb_UTF8 dbo Contact Username
sysdb_UTF8 dbo Extension Active
sysdb_UTF8 dbo Extension AttributeID
sysdb_UTF8 dbo Extension ChangeTime
sysdb_UTF8 dbo Extension ChangeUser
sysdb_UTF8 dbo Extension CreateTime
sysdb_UTF8 dbo Extension CreateUser
sysdb_UTF8 dbo Extension Description
sysdb_UTF8 dbo Extension ID
sysdb_UTF8 dbo Extension Name
sysdb_UTF8 dbo Extension RecordID
sysdb_UTF8 dbo Extension SortIndex
sysdb_UTF8 dbo Extension TableID
sysdb_UTF8 dbo Extension Value
sysdb_UTF8 dbo ExtensionAttribute Active
sysdb_UTF8 dbo ExtensionAttribute ChangeTime
sysdb_UTF8 dbo ExtensionAttribute ChangeUser
sysdb_UTF8 dbo ExtensionAttribute CreateTime
sysdb_UTF8 dbo ExtensionAttribute CreateUser
sysdb_UTF8 dbo ExtensionAttribute Description
sysdb_UTF8 dbo ExtensionAttribute ID
sysdb_UTF8 dbo ExtensionAttribute Name
sysdb_UTF8 dbo ExtensionAttribute SortIndex
sysdb_UTF8 dbo History Active
sysdb_UTF8 dbo History ChangeTime
sysdb_UTF8 dbo History ChangeUser
sysdb_UTF8 dbo History CreateTime
sysdb_UTF8 dbo History CreateUser
sysdb_UTF8 dbo History Description
sysdb_UTF8 dbo History ID
sysdb_UTF8 dbo History Name
sysdb_UTF8 dbo History RecordID
sysdb_UTF8 dbo History SortIndex
sysdb_UTF8 dbo History Tablename
sysdb_UTF8 dbo Import AttributeName
sysdb_UTF8 dbo Import AttributeTyp
sysdb_UTF8 dbo Import AttributeValue
sysdb_UTF8 dbo Import CreateTime
sysdb_UTF8 dbo Import CreateUser
sysdb_UTF8 dbo Import ExternalIdentifier
sysdb_UTF8 dbo Import ID
sysdb_UTF8 dbo Import ImportSource
sysdb_UTF8 dbo Import Status
sysdb_UTF8 dbo Issue Active
sysdb_UTF8 dbo Issue Additionals
sysdb_UTF8 dbo Issue Assignee
sysdb_UTF8 dbo Issue ChangeTime
sysdb_UTF8 dbo Issue ChangeUser
sysdb_UTF8 dbo Issue CreateTime
sysdb_UTF8 dbo Issue CreateUser
sysdb_UTF8 dbo Issue Creator
sysdb_UTF8 dbo Issue Description
sysdb_UTF8 dbo Issue ID
sysdb_UTF8 dbo Issue Name
sysdb_UTF8 dbo Issue Priority
sysdb_UTF8 dbo Issue SortIndex
sysdb_UTF8 dbo Issue Status
sysdb_UTF8 dbo Issue TypID
sysdb_UTF8 dbo Issue UpdateTime
sysdb_UTF8 dbo Issue URL
sysdb_UTF8 dbo Mandant Active
sysdb_UTF8 dbo Mandant ChangeTime
sysdb_UTF8 dbo Mandant ChangeUser
sysdb_UTF8 dbo Mandant City
sysdb_UTF8 dbo Mandant Code
sysdb_UTF8 dbo Mandant ContactID
sysdb_UTF8 dbo Mandant CreateTime
sysdb_UTF8 dbo Mandant CreateUser
sysdb_UTF8 dbo Mandant Description
sysdb_UTF8 dbo Mandant Fullname
sysdb_UTF8 dbo Mandant ID
sysdb_UTF8 dbo Mandant Mail
sysdb_UTF8 dbo Mandant Name
sysdb_UTF8 dbo Mandant Postcode
sysdb_UTF8 dbo Mandant SortIndex
sysdb_UTF8 dbo Mandant Street
sysdb_UTF8 dbo MenuItem Active
sysdb_UTF8 dbo MenuItem Category
sysdb_UTF8 dbo MenuItem ChangeTime
sysdb_UTF8 dbo MenuItem ChangeUser
sysdb_UTF8 dbo MenuItem CreateTime
sysdb_UTF8 dbo MenuItem CreateUser
sysdb_UTF8 dbo MenuItem Description
sysdb_UTF8 dbo MenuItem ID
sysdb_UTF8 dbo MenuItem ImageIndex
sysdb_UTF8 dbo MenuItem Name
sysdb_UTF8 dbo MenuItem SortIndex
sysdb_UTF8 dbo Notification Active
sysdb_UTF8 dbo Notification CategoryID
sysdb_UTF8 dbo Notification ChangeTime
sysdb_UTF8 dbo Notification ChangeUser
sysdb_UTF8 dbo Notification CreateTime
sysdb_UTF8 dbo Notification CreateUser
sysdb_UTF8 dbo Notification Description
sysdb_UTF8 dbo Notification ExpirationDays
sysdb_UTF8 dbo Notification ID
sysdb_UTF8 dbo Notification Name
sysdb_UTF8 dbo Notification RecordID
sysdb_UTF8 dbo Notification ServiceItemID
sysdb_UTF8 dbo Notification SortIndex
sysdb_UTF8 dbo Notification TableID
sysdb_UTF8 dbo Notification Username
sysdb_UTF8 dbo OrgUnit Active
sysdb_UTF8 dbo OrgUnit ChangeTime
sysdb_UTF8 dbo OrgUnit ChangeUser
sysdb_UTF8 dbo OrgUnit CreateTime
sysdb_UTF8 dbo OrgUnit CreateUser
sysdb_UTF8 dbo OrgUnit Description
sysdb_UTF8 dbo OrgUnit Homedirectory
sysdb_UTF8 dbo OrgUnit ID
sysdb_UTF8 dbo OrgUnit Name
sysdb_UTF8 dbo OrgUnit SortIndex
sysdb_UTF8 dbo ServiceItem Active
sysdb_UTF8 dbo ServiceItem CategoryID
sysdb_UTF8 dbo ServiceItem ChangeTime
sysdb_UTF8 dbo ServiceItem ChangeUser
sysdb_UTF8 dbo ServiceItem CreateTime
sysdb_UTF8 dbo ServiceItem CreateUser
sysdb_UTF8 dbo ServiceItem Description
sysdb_UTF8 dbo ServiceItem ID
sysdb_UTF8 dbo ServiceItem InternalID
sysdb_UTF8 dbo ServiceItem Name
sysdb_UTF8 dbo ServiceItem OrgUnitID
sysdb_UTF8 dbo ServiceItem Protected
sysdb_UTF8 dbo ServiceItem SortIndex
sysdb_UTF8 dbo ServiceItem StatusID
sysdb_UTF8 dbo ServiceItem SyncSource
sysdb_UTF8 dbo ServiceItem SyncTime
sysdb_UTF8 dbo ServiceItem TechnicalDescription
sysdb_UTF8 dbo ServiceItemComponentItemRelation Active
sysdb_UTF8 dbo ServiceItemComponentItemRelation ChangeTime
sysdb_UTF8 dbo ServiceItemComponentItemRelation ChangeUser
sysdb_UTF8 dbo ServiceItemComponentItemRelation ComponentItemID
sysdb_UTF8 dbo ServiceItemComponentItemRelation ConsumerRelation
sysdb_UTF8 dbo ServiceItemComponentItemRelation CreateTime
sysdb_UTF8 dbo ServiceItemComponentItemRelation CreateUser
sysdb_UTF8 dbo ServiceItemComponentItemRelation Description
sysdb_UTF8 dbo ServiceItemComponentItemRelation EnvironmentID
sysdb_UTF8 dbo ServiceItemComponentItemRelation ID
sysdb_UTF8 dbo ServiceItemComponentItemRelation ImpactDescription
sysdb_UTF8 dbo ServiceItemComponentItemRelation ImpactTypID
sysdb_UTF8 dbo ServiceItemComponentItemRelation MandantID
sysdb_UTF8 dbo ServiceItemComponentItemRelation Name
sysdb_UTF8 dbo ServiceItemComponentItemRelation ProviderRelation
sysdb_UTF8 dbo ServiceItemComponentItemRelation ServiceItemID
sysdb_UTF8 dbo ServiceItemComponentItemRelation SortIndex
sysdb_UTF8 dbo ServiceItemContactRelation Active
sysdb_UTF8 dbo ServiceItemContactRelation ChangeTime
sysdb_UTF8 dbo ServiceItemContactRelation ChangeUser
sysdb_UTF8 dbo ServiceItemContactRelation ContactID
sysdb_UTF8 dbo ServiceItemContactRelation ContactTypID
sysdb_UTF8 dbo ServiceItemContactRelation CreateTime
sysdb_UTF8 dbo ServiceItemContactRelation CreateUser
sysdb_UTF8 dbo ServiceItemContactRelation Description
sysdb_UTF8 dbo ServiceItemContactRelation ID
sysdb_UTF8 dbo ServiceItemContactRelation MandantID
sysdb_UTF8 dbo ServiceItemContactRelation Name
sysdb_UTF8 dbo ServiceItemContactRelation ServiceItemID
sysdb_UTF8 dbo ServiceItemContactRelation SortIndex
sysdb_UTF8 dbo ServiceItemFile Active
sysdb_UTF8 dbo ServiceItemFile CategoryID
sysdb_UTF8 dbo ServiceItemFile ChangeTime
sysdb_UTF8 dbo ServiceItemFile ChangeUser
sysdb_UTF8 dbo ServiceItemFile CreateTime
sysdb_UTF8 dbo ServiceItemFile CreateUser
sysdb_UTF8 dbo ServiceItemFile Description
sysdb_UTF8 dbo ServiceItemFile FileChangeTime
sysdb_UTF8 dbo ServiceItemFile FileCreateTime
sysdb_UTF8 dbo ServiceItemFile Filepath
sysdb_UTF8 dbo ServiceItemFile FileSize
sysdb_UTF8 dbo ServiceItemFile Filetyp
sysdb_UTF8 dbo ServiceItemFile ID
sysdb_UTF8 dbo ServiceItemFile MandantID
sysdb_UTF8 dbo ServiceItemFile Name
sysdb_UTF8 dbo ServiceItemFile ServiceItemID
sysdb_UTF8 dbo ServiceItemFile SortIndex
sysdb_UTF8 dbo ServiceItemFile Visible
sysdb_UTF8 dbo ServiceItemIssueRelation Active
sysdb_UTF8 dbo ServiceItemIssueRelation ChangeTime
sysdb_UTF8 dbo ServiceItemIssueRelation ChangeUser
sysdb_UTF8 dbo ServiceItemIssueRelation CreateTime
sysdb_UTF8 dbo ServiceItemIssueRelation CreateUser
sysdb_UTF8 dbo ServiceItemIssueRelation Description
sysdb_UTF8 dbo ServiceItemIssueRelation ID
sysdb_UTF8 dbo ServiceItemIssueRelation IssueID
sysdb_UTF8 dbo ServiceItemIssueRelation MandantID
sysdb_UTF8 dbo ServiceItemIssueRelation Name
sysdb_UTF8 dbo ServiceItemIssueRelation ServiceItemID
sysdb_UTF8 dbo ServiceItemIssueRelation SortIndex
sysdb_UTF8 dbo ServiceItemKnowledge Active
sysdb_UTF8 dbo ServiceItemKnowledge CategoryID
sysdb_UTF8 dbo ServiceItemKnowledge ChangeTime
sysdb_UTF8 dbo ServiceItemKnowledge ChangeUser
sysdb_UTF8 dbo ServiceItemKnowledge CreateTime
sysdb_UTF8 dbo ServiceItemKnowledge CreateUser
sysdb_UTF8 dbo ServiceItemKnowledge Description
sysdb_UTF8 dbo ServiceItemKnowledge DescriptionRtf
sysdb_UTF8 dbo ServiceItemKnowledge ExternalUrl
sysdb_UTF8 dbo ServiceItemKnowledge ID
sysdb_UTF8 dbo ServiceItemKnowledge MandantID
sysdb_UTF8 dbo ServiceItemKnowledge Name
sysdb_UTF8 dbo ServiceItemKnowledge ServiceItemID
sysdb_UTF8 dbo ServiceItemKnowledge SortIndex
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation Active
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation ChangeTime
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation ChangeUser
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation CreateTime
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation CreateUser
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation Description
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation ID
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation Name
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation ServiceItemFileID
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation ServiceItemKnowledgeID
sysdb_UTF8 dbo ServiceItemKnowledgeServiceItemFileRelation SortIndex
sysdb_UTF8 dbo ServiceItemLog Active
sysdb_UTF8 dbo ServiceItemLog ChangeTime
sysdb_UTF8 dbo ServiceItemLog ChangeUser
sysdb_UTF8 dbo ServiceItemLog Content
sysdb_UTF8 dbo ServiceItemLog CreateTime
sysdb_UTF8 dbo ServiceItemLog CreateUser
sysdb_UTF8 dbo ServiceItemLog Description
sysdb_UTF8 dbo ServiceItemLog ID
sysdb_UTF8 dbo ServiceItemLog MandantID
sysdb_UTF8 dbo ServiceItemLog Name
sysdb_UTF8 dbo ServiceItemLog ServiceItemID
sysdb_UTF8 dbo ServiceItemLog SortIndex
sysdb_UTF8 dbo ServiceItemLog Tag
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation Active
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation ChangeTime
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation ChangeUser
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation CreateTime
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation CreateUser
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation Description
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation ID
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation Name
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation ServiceItemFileID
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation ServiceItemLogID
sysdb_UTF8 dbo ServiceItemLogServiceItemFileRelation SortIndex
sysdb_UTF8 dbo ServiceItemMandantRelation Account
sysdb_UTF8 dbo ServiceItemMandantRelation Active
sysdb_UTF8 dbo ServiceItemMandantRelation CategoryID
sysdb_UTF8 dbo ServiceItemMandantRelation ChangeTime
sysdb_UTF8 dbo ServiceItemMandantRelation ChangeUser
sysdb_UTF8 dbo ServiceItemMandantRelation CreateTime
sysdb_UTF8 dbo ServiceItemMandantRelation CreateUser
sysdb_UTF8 dbo ServiceItemMandantRelation Description
sysdb_UTF8 dbo ServiceItemMandantRelation EndDate
sysdb_UTF8 dbo ServiceItemMandantRelation ID
sysdb_UTF8 dbo ServiceItemMandantRelation MandantID
sysdb_UTF8 dbo ServiceItemMandantRelation Name
sysdb_UTF8 dbo ServiceItemMandantRelation ServiceItemID
sysdb_UTF8 dbo ServiceItemMandantRelation SortIndex
sysdb_UTF8 dbo ServiceItemMandantRelation StartDate
sysdb_UTF8 dbo ServiceItemMandantRelation Timebudget
sysdb_UTF8 dbo ServiceItemMandantRelation Usercount
sysdb_UTF8 dbo Systemlog AppName
sysdb_UTF8 dbo Systemlog CreateTime
sysdb_UTF8 dbo Systemlog CreateUser
sysdb_UTF8 dbo Systemlog ID
sysdb_UTF8 dbo Systemlog LogLevel
sysdb_UTF8 dbo Systemlog Message
sysdb_UTF8 dbo Systemlog Source
sysdb_UTF8 dbo Systemsetting Active
sysdb_UTF8 dbo Systemsetting Category
sysdb_UTF8 dbo Systemsetting ChangeTime
sysdb_UTF8 dbo Systemsetting ChangeUser
sysdb_UTF8 dbo Systemsetting CreateTime
sysdb_UTF8 dbo Systemsetting CreateUser
sysdb_UTF8 dbo Systemsetting Description
sysdb_UTF8 dbo Systemsetting ID
sysdb_UTF8 dbo Systemsetting Name
sysdb_UTF8 dbo Systemsetting SortIndex
sysdb_UTF8 dbo Systemsetting Value
sysdb_UTF8 dbo Systemsetting Value2
sysdb_UTF8 dbo Tag Active
sysdb_UTF8 dbo Tag CategoryID
sysdb_UTF8 dbo Tag ChangeTime
sysdb_UTF8 dbo Tag ChangeUser
sysdb_UTF8 dbo Tag CreateTime
sysdb_UTF8 dbo Tag CreateUser
sysdb_UTF8 dbo Tag Description
sysdb_UTF8 dbo Tag ID
sysdb_UTF8 dbo Tag Name
sysdb_UTF8 dbo Tag SortIndex
sysdb_UTF8 dbo TagRelation Active
sysdb_UTF8 dbo TagRelation ChangeTime
sysdb_UTF8 dbo TagRelation ChangeUser
sysdb_UTF8 dbo TagRelation CreateTime
sysdb_UTF8 dbo TagRelation CreateUser
sysdb_UTF8 dbo TagRelation Description
sysdb_UTF8 dbo TagRelation ID
sysdb_UTF8 dbo TagRelation Name
sysdb_UTF8 dbo TagRelation RecordID
sysdb_UTF8 dbo TagRelation SortIndex
sysdb_UTF8 dbo TagRelation TableID
sysdb_UTF8 dbo TagRelation TagID
sysdb_UTF8 dbo TempTableServer Value
sysdb_UTF8 dbo Usersetting Active
sysdb_UTF8 dbo Usersetting ChangeTime
sysdb_UTF8 dbo Usersetting ChangeUser
sysdb_UTF8 dbo Usersetting CreateTime
sysdb_UTF8 dbo Usersetting CreateUser
sysdb_UTF8 dbo Usersetting Description
sysdb_UTF8 dbo Usersetting ID
sysdb_UTF8 dbo Usersetting Name
sysdb_UTF8 dbo Usersetting SortIndex
sysdb_UTF8 dbo Usersetting Username
sysdb_UTF8 dbo Usersetting Value
and the script to copy all tables and columns to a new db with utf-8 collation:
USE \[sysdb_utf8_jr\]
GO
\-- Trigger in neuer Zieldatenbank deaktivieren damit ChangeUser und ChangeTime beim Import nicht überschrieben werden
DISABLE TRIGGER tr_update_OrgUnit ON OrgUnit;
DISABLE TRIGGER tr_update_Usersetting ON Usersetting;
DISABLE TRIGGER tr_update_Systemsetting ON Systemsetting;
DISABLE TRIGGER tr_update_Extension ON Extension;
DISABLE TRIGGER tr_update_History ON History;
DISABLE TRIGGER tr_update_MenuItem ON MenuItem;
\--DISABLE TRIGGER tr_update_Import ON Import;
DISABLE TRIGGER tr_update_Contact ON Contact;
DISABLE TRIGGER tr_update_ServiceItem ON ServiceItem;
DISABLE TRIGGER tr_update_ServiceItemLog ON ServiceItemLog;
DISABLE TRIGGER tr_update_ServiceItemKnowledge ON ServiceItemKnowledge;
DISABLE TRIGGER tr_update_ServiceItemFile ON ServiceItemFile;
DISABLE TRIGGER tr_update_Service ON \[Service\];
DISABLE TRIGGER tr_update_Mandant ON Mandant;
DISABLE TRIGGER tr_update_ComponentItemTyp ON ComponentItemTyp;
DISABLE TRIGGER tr_update_ComponentItem ON ComponentItem;
DISABLE TRIGGER tr_update_Issue ON Issue;
DISABLE TRIGGER tr_update_ComponentItemRelationTyp ON ComponentItemRelationTyp;
DISABLE TRIGGER tr_update_ComponentItemRelation ON ComponentItemRelation;
DISABLE TRIGGER tr_update_ServiceItemContactRelation ON ServiceItemContactRelation;
DISABLE TRIGGER tr_update_ServiceItemComponentItemRelation ON ServiceItemComponentItemRelation;
DISABLE TRIGGER tr_update_ServiceItemServiceRelation ON ServiceItemServiceRelation;
DISABLE TRIGGER tr_update_ServiceItemMandantRelation ON ServiceItemMandantRelation;
DISABLE TRIGGER tr_update_ServiceItemIssueRelation ON ServiceItemIssueRelation;
DISABLE TRIGGER tr_update_ServiceItemKnowledgeServiceItemFileRelation ON ServiceItemKnowledgeServiceItemFileRelation;
DISABLE TRIGGER tr_update_ServiceItemLogServiceItemFileRelation ON ServiceItemLogServiceItemFileRelation;
GO
\-- Stammdatensätze löschen
DELETE FROM ComponentItemTyp
DELETE FROM MenuItem
\-- Daten importieren
SET IDENTITY_INSERT OrgUnit ON
INSERT INTO OrgUnit
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Homedirectory
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Homedirectory
FROM
\[sysdb_UTF8\].dbo.OrgUnit
SET IDENTITY_INSERT OrgUnit OFF
INSERT INTO Usersetting
(
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Username,
\[Value\]
)
SELECT
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Username,
\[Value\]
FROM
\[sysdb_UTF8\].dbo.Usersetting
INSERT INTO Systemsetting
(
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Category,
\[Value\],
\[Value2\]
)
SELECT
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Category,
\[Value\],
\[Value2\]
FROM
\[sysdb_UTF8\].dbo.Systemsetting
INSERT INTO MenuItem
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Category,
ImageIndex
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Category,
ImageIndex
FROM
\[sysdb_UTF8\].dbo.MenuItem
\--SET IDENTITY_INSERT Import ON
\--INSERT INTO Import
\-- (
\-- ID,
\-- CreateTime,
\-- CreateUser,
\-- ChangeTime,
\-- ChangeUser,
\-- ImportSource,
\-- ExternalIdentifier,
\-- AttributeName,
\-- AttributeTyp,
\-- AttributeValue,
\-- \[Status\]
-- )
-- SELECT
-- ID,
-- CreateTime,
-- CreateUser,
-- ChangeTime,
-- ChangeUser,
-- ImportSource,
-- ExternalIdentifier,
-- AttributeName,
-- AttributeTyp,
-- AttributeValue,
-- \[Status\]
-- FROM
-- \[sysdb_UTF8\].dbo.Import
--SET IDENTITY_INSERT Import OFF
SET IDENTITY_INSERT Extension ON
INSERT INTO Extension
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
\[Value\],
TableID,
RecordID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
\[Value\],
TableID,
RecordID
FROM
\[sysdb_UTF8\].dbo.Extension
SET IDENTITY_INSERT Extension OFF
SET IDENTITY_INSERT History ON
INSERT INTO History
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Tablename,
RecordID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Tablename,
RecordID
FROM
\[sysdb_UTF8\].dbo.History
SET IDENTITY_INSERT History OFF
SET IDENTITY_INSERT Contact ON
INSERT INTO Contact
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Mail,
Phone,
Mobile,
Adress,
Company,
Username,
Firstname,
Lastname,
SyncTime,
SyncSource
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Mail,
Phone,
Mobile,
Adress,
Company,
Username,
Firstname,
Lastname,
SyncTime,
SyncSource
FROM
\[sysdb_UTF8\].dbo.Contact
SET IDENTITY_INSERT Contact OFF
SET IDENTITY_INSERT Mandant ON
INSERT INTO Mandant
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Fullname,
Code,
Postcode,
City,
Street,
Mail,
ContactID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Fullname,
Code,
Postcode,
City,
Street,
Mail,
ContactID
FROM
\[sysdb_UTF8\].dbo.Mandant
SET IDENTITY_INSERT Mandant OFF
SET IDENTITY_INSERT ServiceItem ON
INSERT INTO ServiceItem
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
InternalID,
TechnicalDescription,
OrgUnitID,
StatusID,
Protected
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
InternalID,
TechnicalDescription,
OrgUnitID,
StatusID,
Protected
FROM
\[sysdb_UTF8\].dbo.ServiceItem
SET IDENTITY_INSERT ServiceItem OFF
SET IDENTITY_INSERT ServiceItemLog ON
INSERT INTO ServiceItemLog
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Tag,
Content,
ServiceItemID,
MandantID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Tag,
Content,
ServiceItemID,
MandantID
FROM
\[sysdb_UTF8\].dbo.ServiceItemLog
SET IDENTITY_INSERT ServiceItemLog OFF
SET IDENTITY_INSERT ServiceItemKnowledge ON
INSERT INTO ServiceItemKnowledge
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
DescriptionRtf,
CategoryID,
ServiceItemID,
MandantID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
DescriptionRtf,
CategoryID,
ServiceItemID,
MandantID
FROM
\[sysdb_UTF8\].dbo.ServiceItemKnowledge
SET IDENTITY_INSERT ServiceItemKnowledge OFF
SET IDENTITY_INSERT ServiceItemFile ON
INSERT INTO ServiceItemFile
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Filepath,
Filetyp,
FileCreateTime,
FileChangeTime,
FileSize,
CategoryID,
ServiceItemID,
MandantID,
Visible
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Filepath,
Filetyp,
FileCreateTime,
FileChangeTime,
FileSize,
CategoryID,
ServiceItemID,
MandantID,
Visible
FROM
\[sysdb_UTF8\].dbo.ServiceItemFile
SET IDENTITY_INSERT ServiceItemFile OFF
\--SET IDENTITY_INSERT \[Service\] ON
--INSERT INTO \[Service\]
-- (
-- ID,
-- CreateTime,
-- CreateUser,
-- ChangeTime,
-- ChangeUser,
-- SortIndex,
-- Active,
-- \[Name\],
-- \[Description\]
-- )
-- SELECT
-- ID,
-- CreateTime,
-- CreateUser,
-- ChangeTime,
-- ChangeUser,
-- SortIndex,
-- Active,
-- \[Name\],
-- \[Description\]
-- FROM
-- \[sysdb_UTF8\].dbo.\[Service\]
SET IDENTITY_INSERT \[Service\] OFF
SET IDENTITY_INSERT ComponentItemTyp ON
INSERT INTO ComponentItemTyp
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
InterfaceAble,
ImageIndex,
NameLabel,
NameDefault,
AdditionalValueLabel1,
AdditionalValueOption1,
AdditionalValueLabel2,
AdditionalValueOption2,
FlexValueLabel1,
FlexValueOption1,
FlexValueLabel2,
FlexValueOption2,
FlexValueLabel3,
FlexValueOption3,
FlexValueLabel4,
FlexValueOption4,
FlexValueLabel5,
FlexValueOption5,
FlexValueLabel6,
FlexValueOption6,
FlexValueLabel7,
FlexValueOption7,
FlexValueLabel8,
FlexValueOption8
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
InterfaceAble,
ImageIndex,
NameLabel,
NameDefault,
AdditionalValueLabel1,
AdditionalValueOption1,
AdditionalValueLabel2,
AdditionalValueOption2,
FlexValueLabel1,
FlexValueOption1,
FlexValueLabel2,
FlexValueOption2,
FlexValueLabel3,
FlexValueOption3,
FlexValueLabel4,
FlexValueOption4,
FlexValueLabel5,
FlexValueOption5,
FlexValueLabel6,
FlexValueOption6,
FlexValueLabel7,
FlexValueOption7,
FlexValueLabel8,
FlexValueOption8
FROM
\[sysdb_UTF8\].dbo.ComponentItemTyp;
SET IDENTITY_INSERT ComponentItemTyp OFF
SET IDENTITY_INSERT ComponentItem ON
INSERT INTO ComponentItem
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
AdditionalValue1,
AdditionalValue2,
TypID,
StatusID,
FlexValue1,
FlexValue2,
FlexValue3,
FlexValue4,
FlexValue5,
FlexValue6,
FlexValue7,
FlexValue8,
FlexValue9,
FlexValue10,
SyncTime,
SyncSource
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
isnull(AdditionalValue1,''),
isnull(AdditionalValue2,''),
TypID,
StatusID,
FlexValue1,
FlexValue2,
FlexValue3,
FlexValue4,
FlexValue5,
FlexValue6,
FlexValue7,
FlexValue8,
FlexValue9,
FlexValue10,
SyncTime,
SyncSource
FROM
\[sysdb_UTF8\].dbo.ComponentItem
SET IDENTITY_INSERT ComponentItem OFF
SET IDENTITY_INSERT Issue ON
INSERT INTO Issue
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Creator,
Assignee,
\[Priority\],
\[Status\],
UpdateTime,
\[URL\],
TypID,
Additionals
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
Creator,
Assignee,
\[Priority\],
\[Status\],
UpdateTime,
\[URL\],
TypID,
Additionals
FROM
\[sysdb_UTF8\].dbo.Issue
SET IDENTITY_INSERT Issue OFF
SET IDENTITY_INSERT ComponentItemRelationTyp ON
INSERT INTO ComponentItemRelationTyp
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
DefaultDescription,
DefaultPort,
DefaultProtocol
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
DefaultDescription,
DefaultPort,
DefaultProtocol
FROM
\[sysdb_UTF8\].dbo.ComponentItemRelationTyp
SET IDENTITY_INSERT ComponentItemRelationTyp OFF
SET IDENTITY_INSERT ComponentItemRelation ON
INSERT INTO ComponentItemRelation
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ComponentItemAID,
ComponentItemBID,
\[Port\],
Protocol,
Direction,
Interface,
TypID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ComponentItemAID,
ComponentItemBID,
\[Port\],
Protocol,
Direction,
Interface,
TypID
FROM
\[sysdb_UTF8\].dbo.ComponentItemRelation
SET IDENTITY_INSERT ComponentItemRelation OFF
SET IDENTITY_INSERT ServiceItemContactRelation ON
INSERT INTO ServiceItemContactRelation
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ContactID,
ServiceItemID,
ContactTypID,
MandantID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ContactID,
ServiceItemID,
ContactTypID,
MandantID
FROM
\[sysdb_UTF8\].dbo.ServiceItemContactRelation
SET IDENTITY_INSERT ServiceItemContactRelation OFF
SET IDENTITY_INSERT ServiceItemComponentItemRelation ON
INSERT INTO ServiceItemComponentItemRelation
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ServiceItemID,
ComponentItemID,
EnvironmentID,
MandantID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ServiceItemID,
ComponentItemID,
EnvironmentID,
MandantID
FROM
\[sysdb_UTF8\].dbo.ServiceItemComponentItemRelation
SET IDENTITY_INSERT ServiceItemComponentItemRelation OFF
\--SET IDENTITY_INSERT ServiceItemServiceRelation ON
\--INSERT INTO ServiceItemServiceRelation
\-- (
\-- ID,
\-- CreateTime,
\-- CreateUser,
\-- ChangeTime,
\-- ChangeUser,
\-- SortIndex,
\-- Active,
\-- \[Name\],
-- \[Description\],
-- ServiceID,
-- ServiceItemID
-- )
-- SELECT
-- ID,
-- CreateTime,
-- CreateUser,
-- ChangeTime,
-- ChangeUser,
-- SortIndex,
-- Active,
-- \[Name\],
-- \[Description\],
-- ServiceID,
-- ServiceItemID
-- FROM
-- \[sysdb_UTF8\].dbo.ServiceItemServiceRelation
--SET IDENTITY_INSERT ServiceItemServiceRelation OFF
SET IDENTITY_INSERT ServiceItemMandantRelation ON
INSERT INTO ServiceItemMandantRelation
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
MandantID,
ServiceItemID,
CategoryID,
Account,
Timebudget,
Usercount
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
MandantID,
ServiceItemID,
CategoryID,
Account,
Timebudget,
Usercount
FROM
\[sysdb_UTF8\].dbo.ServiceItemMandantRelation
SET IDENTITY_INSERT ServiceItemMandantRelation OFF
SET IDENTITY_INSERT ServiceItemIssueRelation ON
INSERT INTO ServiceItemIssueRelation
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ServiceItemID,
IssueID,
MandantID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ServiceItemID,
IssueID,
MandantID
FROM
\[sysdb_UTF8\].dbo.ServiceItemIssueRelation
SET IDENTITY_INSERT ServiceItemIssueRelation OFF
SET IDENTITY_INSERT ServiceItemKnowledgeServiceItemFileRelation ON
INSERT INTO ServiceItemKnowledgeServiceItemFileRelation
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ServiceItemKnowledgeID,
ServiceItemFileID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ServiceItemKnowledgeID,
ServiceItemFileID
FROM
\[sysdb_UTF8\].dbo.ServiceItemKnowledgeServiceItemFileRelation
SET IDENTITY_INSERT ServiceItemKnowledgeServiceItemFileRelation OFF
SET IDENTITY_INSERT ServiceItemLogServiceItemFileRelation ON
INSERT INTO ServiceItemLogServiceItemFileRelation
(
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ServiceItemlogID,
ServiceItemFileID
)
SELECT
ID,
CreateTime,
CreateUser,
ChangeTime,
ChangeUser,
SortIndex,
Active,
\[Name\],
\[Description\],
ServiceItemlogID,
ServiceItemFileID
FROM
\[sysdb_UTF8\].dbo.ServiceItemLogServiceItemFileRelation
SET IDENTITY_INSERT ServiceItemLogServiceItemFileRelation OFF
GO
\---- Mandantenzuordnung
\--UPDATE ServiceItemContactRelation SET MandantID = 1 -- BGP
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@bg-phoenics.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 2 -- BGBAU
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@bgbau.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 3 -- BFGA
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@bfga.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 4 -- BGN
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@bgn.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 5 -- BGRCI
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@bgrci.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 6 -- UKBB
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@ukbb.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 7 -- UKHB
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%ukbremen.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 8 -- UKMV
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%uk-mv.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 9 -- UKN
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%uk-nord.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 10 -- UKNRW
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@unfallkasse-nrw.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 11 -- UKRLP
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@ukrlp.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 12 -- UKSL
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@uks.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 13 -- UKSN
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@uksachsen.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 14 -- UKBW
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@ukbw.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 16 -- HFUKN
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@hfuk-nord.de') and ContactTypID = '313'
\--UPDATE ServiceItemContactRelation SET MandantID = 17 -- HFKM
\--where contactid in (SELECT ID from v_Contact WHERE mail like '%@fuk-mitte.de') and ContactTypID = '313'
\--GO
\-- Trigger wieder aktivieren
ENABLE TRIGGER tr_update_OrgUnit ON OrgUnit;
ENABLE TRIGGER tr_update_Usersetting ON Usersetting;
ENABLE TRIGGER tr_update_Systemsetting ON Systemsetting;
ENABLE TRIGGER tr_update_Extension ON Extension;
ENABLE TRIGGER tr_update_History ON History;
ENABLE TRIGGER tr_update_MenuItem ON MenuItem;
\--ENABLE TRIGGER tr_update_Import ON Import;
ENABLE TRIGGER tr_update_Contact ON Contact;
ENABLE TRIGGER tr_update_ServiceItem ON ServiceItem;
ENABLE TRIGGER tr_update_ServiceItemLog ON ServiceItemLog;
ENABLE TRIGGER tr_update_ServiceItemKnowledge ON ServiceItemKnowledge;
ENABLE TRIGGER tr_update_ServiceItemFile ON ServiceItemFile;
ENABLE TRIGGER tr_update_Service ON \[Service\];
ENABLE TRIGGER tr_update_Mandant ON Mandant;
ENABLE TRIGGER tr_update_ComponentItemTyp ON ComponentItemTyp;
ENABLE TRIGGER tr_update_ComponentItem ON ComponentItem;
ENABLE TRIGGER tr_update_Issue ON Issue;
ENABLE TRIGGER tr_update_ComponentItemRelationTyp ON ComponentItemRelationTyp;
ENABLE TRIGGER tr_update_ComponentItemRelation ON ComponentItemRelation;
ENABLE TRIGGER tr_update_ServiceItemContactRelation ON ServiceItemContactRelation;
ENABLE TRIGGER tr_update_ServiceItemComponentItemRelation ON ServiceItemComponentItemRelation;
ENABLE TRIGGER tr_update_ServiceItemServiceRelation ON ServiceItemServiceRelation;
ENABLE TRIGGER tr_update_ServiceItemMandantRelation ON ServiceItemMandantRelation;
ENABLE TRIGGER tr_update_ServiceItemIssueRelation ON ServiceItemIssueRelation;
ENABLE TRIGGER tr_update_ServiceItemKnowledgeServiceItemFileRelation ON ServiceItemKnowledgeServiceItemFileRelation;
ENABLE TRIGGER tr_update_ServiceItemLogServiceItemFileRelation ON ServiceItemLogServiceItemFileRelation;
GO
\-- Anzahl aller Datensätze ausgeben lassen (NEU)
SELECT
t.\[Name\] table_name ,
p.rows table_rows
FROM
sys.tables t
JOIN
sys.partitions p
ON
t.object_id = p.object_id
WHERE
p.index_id IN ( 0,1 )
ORDER BY
table_name
\-- Anzahl aller Datensätze ausgeben lassen (ALT)
USE \[sysdb_UTF8\]
GO
SELECT
t.\[Name\] table_name,
p.rows table_rows
FROM
sys.tables t
JOIN
sys.partitions p
ON
t.object_id = p.object_id
WHERE
p.index_id IN ( 0,1 )
ORDER BY
table_name
Please check which tables / columns are missing in the copy script and generate proper select (..) INTO ... statements to insert the data generically into the new db.
Reference in New Issue View Git Blame Copy Permalink