· 8 years ago · Oct 18, 2017, 01:44 PM
1DECLARE @LINKED_SERVER_NAME as varchar(max)
2SET @LINKED_SERVER_NAME = 'WTB-SRV05\SIMMA'
3DECLARE @USERNAME as varchar(max)
4SET @USERNAME = 'cmsnet'
5DECLARE @PASSWORD as varchar(max)
6SET @PASSWORD = ''
7
8DECLARE @TABLE_NAME AS NVARCHAR(MAX)
9SET @TABLE_NAME = 'asset'
10
11-- Erstellen von Linked Server, wenn nicht vorhanden
12if not exists(select server_id from sys.servers where name = @LINKED_SERVER_NAME)
13begin
14 /****** Object: LinkedServer [WTB-SRV05\SIMMA] Script Date: 18.10.2017 13:34:06 ******/
15 EXEC master.dbo.sp_addlinkedserver @server =@LINKED_SERVER_NAME, @srvproduct=N'SQL Server'
16 /* For security reasons the linked server remote logins password is changed with ######## */
17 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@LINKED_SERVER_NAME,@useself=N'False',@locallogin=NULL,@rmtuser=@USERNAME,@rmtpassword=@PASSWORD
18end
19
20-- Erstellen der Tabellenstruktur, wenn nicht vorhanden
21-- Auslesen der Struktur am Linked Server
22-- Erstellen der Tabellen auf diesem Server
23IF EXISTS (select * from sys.objects where name = (select distinct columns.table_name from openquery([WTB-SRV05\SIMMA], 'select t.name as table_name from sys.objects o inner join (select object_id, name from sys.objects) t on o.parent_object_id = t.object_id inner join sys.all_columns c on c.object_id = t.object_id') columns where columns.table_name = @TABLE_NAME))
24begin
25 print 'Tabellenstruktur vorhanden'
26end
27else
28begin
29 print 'Tabellenstruktur nicht vorhanden'
30
31 DECLARE @SQL AS NVARCHAR(MAX)
32 SET @SQL = 'CREATE TABLE ' + @TABLE_NAME + ' ( '
33
34 DECLARE @COLUMN_NAME sysname
35 DECLARE @DATA_TYPE sysname
36 DECLARE @MAX_LENGTH smallint
37 DECLARE @PRECISION tinyint
38 DECLARE @SCALE tinyint
39 DECLARE @IS_NULLABLE bit
40
41 DECLARE cur_columns CURSOR READ_ONLY
42 FOR
43 select columns.name as columnname, columns.datatype, columns.max_length,
44 columns.precision, columns.scale, columns.is_nullable
45 from openquery([WTB-SRV05\SIMMA],
46 'select c.*, types.name as datatype, t.table_name from sys.objects o inner join (select object_id, name as table_name from sys.objects) t on o.parent_object_id = t.object_id inner join sys.all_columns c on c.object_id = t.object_id inner join sys.types types on c.system_type_id = types.system_type_id'
47 ) columns where columns.datatype != 'sysname' and columns.table_name = @TABLE_NAME
48
49 OPEN cur_columns
50 FETCH NEXT
51 FROM cur_columns
52 INTO @COLUMN_NAME,@DATA_TYPE, @MAX_LENGTH, @PRECISION, @SCALE, @IS_NULLABLE
53
54 WHILE (@@FETCH_STATUS <> -1)
55 BEGIN
56 SET @SQL = @SQL + '[' + @COLUMN_NAME + '] [' + @DATA_TYPE + ']'
57
58 IF @DATA_TYPE = 'varchar' OR @DATA_TYPE = 'nvarchar' OR @DATA_TYPE = 'char' OR @DATA_TYPE = 'nchar'
59 BEGIN
60 set @SQL = @SQL + '(' + cast(@MAX_LENGTH / 2 as nvarchar(max)) + ')'
61 END
62
63 IF @IS_NULLABLE = 0
64 BEGIN
65 set @SQL = @SQL + ' NOT NULL'
66 END
67 ELSE
68 BEGIN
69 set @SQL = @SQL + ' NULL'
70 END
71
72 SET @SQL = @SQL + ','+ CHAR(10)
73
74 FETCH NEXT
75 FROM cur_columns
76 INTO @COLUMN_NAME,@DATA_TYPE, @MAX_LENGTH, @PRECISION, @SCALE, @IS_NULLABLE
77 END
78
79 SET @SQL = CAST (SUBSTRING(@SQL, 1, LEN(@SQL)-2) as nvarchar(max)) + ')'
80
81 CLOSE cur_columns
82 DEALLOCATE cur_columns
83
84 print @SQL
85end