|
1
|
BEGIN TRANSACTION
|
|
2
|
BEGIN TRY
|
|
3
|
DECLARE @TableName VARCHAR(100),
|
|
4
|
@Prefix NVARCHAR(10),
|
|
5
|
@ColumnName NVARCHAR(128),
|
|
6
|
@lst_TableName_Select NVARCHAR(MAX) = '',
|
|
7
|
@lst_TableName_Not_Select NVARCHAR(MAX) = '',
|
|
8
|
@lst_TableName_Fail NVARCHAR(MAX) = '',
|
|
9
|
@NEW_LINE CHAR(4) = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
|
|
10
|
|
|
11
|
DECLARE Detail CURSOR FOR
|
|
12
|
SELECT SP.ID, SP.Prefix, KCU.COLUMN_NAME
|
|
13
|
FROM dbo.SYS_PREFIX SP
|
|
14
|
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.TABLE_NAME = SP.ID
|
|
15
|
WHERE ID NOT IN (SELECT A.TABLE_NAME
|
|
16
|
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
|
|
17
|
LEFT JOIN INFORMATION_SCHEMA.TABLES B ON A.TABLE_NAME = B.TABLE_NAME
|
|
18
|
WHERE OBJECTPROPERTY(OBJECT_ID(A.CONSTRAINT_SCHEMA + '.' + QUOTENAME(A.CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
|
|
19
|
GROUP BY A.TABLE_NAME
|
|
20
|
HAVING COUNT(A.COLUMN_NAME) > 1)
|
|
21
|
AND OBJECTPROPERTY(OBJECT_ID(KCU.CONSTRAINT_SCHEMA + '.' + QUOTENAME(KCU.CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
|
|
22
|
OPEN Detail
|
|
23
|
FETCH NEXT FROM Detail INTO @TableName,@Prefix,@ColumnName
|
|
24
|
WHILE @@FETCH_STATUS = 0
|
|
25
|
BEGIN
|
|
26
|
DECLARE @sql NVARCHAR(MAX),
|
|
27
|
@TableName_Fail VARCHAR(100)
|
|
28
|
SET @sql = '
|
|
29
|
DECLARE @VALUE VARCHAR(25) = NULL
|
|
30
|
--
|
|
31
|
SELECT TOP 1 @VALUE = '+@ColumnName+'
|
|
32
|
FROM '+@TableName+'
|
|
33
|
WHERE 1=1
|
|
34
|
AND REPLACE('+@ColumnName+','''+@Prefix+''','''') NOT LIKE ''%[^0-9]%''
|
|
35
|
ORDER BY CAST(REPLACE('+@ColumnName+','''+@Prefix+''','''') AS NUMERIC) DESC
|
|
36
|
--
|
|
37
|
IF(NOT EXISTS(SELECT 1 FROM SYS_CODEMASTERS WHERE Prefix = '''+@Prefix+'''))
|
|
38
|
BEGIN
|
|
39
|
INSERT INTO SYS_CODEMASTERS (Prefix,CurValue,[Description],Active)
|
|
40
|
VALUES('''+@Prefix+''',0,'''',1)
|
|
41
|
END
|
|
42
|
--
|
|
43
|
IF (@VALUE IS NOT NULL OR (@VALUE IS NULL AND NOT EXISTS (SELECT TOP 1 * FROM '+@TableName+')))
|
|
44
|
BEGIN
|
|
45
|
UPDATE SYS_CODEMASTERS
|
|
46
|
SET CurValue=(SELECT REPLACE(ISNULL(@VALUE,0),'''+@Prefix+''',''''))
|
|
47
|
WHERE Prefix='''+@Prefix+'''
|
|
48
|
--
|
|
49
|
SET @TableName_Fail = ''''
|
|
50
|
END
|
|
51
|
ELSE
|
|
52
|
BEGIN
|
|
53
|
SET @TableName_Fail = '''+@TableName+'''
|
|
54
|
END
|
|
55
|
'
|
|
56
|
EXEC sp_executesql @sql, N'@TableName_Fail VARCHAR(100) OUT', @TableName_Fail OUT
|
|
57
|
|
|
58
|
IF @TableName_Fail <> ''
|
|
59
|
BEGIN
|
|
60
|
SET @lst_TableName_Fail = CONCAT(@lst_TableName_Fail, ', ', @TableName_Fail)
|
|
61
|
END
|
|
62
|
SET @lst_TableName_Select = CONCAT(@lst_TableName_Select, ',', '''', @TableName, '''')
|
|
63
|
|
|
64
|
FETCH NEXT FROM Detail INTO @TableName,@Prefix,@ColumnName
|
|
65
|
END
|
|
66
|
--
|
|
67
|
SET @lst_TableName_Select = STUFF(@lst_TableName_Select,1,1,'')
|
|
68
|
SELECT @lst_TableName_Not_Select = STUFF((SELECT ', ' + ID
|
|
69
|
FROM dbo.SYS_PREFIX
|
|
70
|
WHERE CHARINDEX('''' + ID + '''', @lst_TableName_Select) = 0
|
|
71
|
FOR XML PATH(''), TYPE).value('.[1]', 'NVARCHAR(MAX)'),1,2,'')
|
|
72
|
--
|
|
73
|
PRINT CONCAT(@NEW_LINE, 'SUCCESS', @NEW_LINE)
|
|
74
|
PRINT CONCAT('Table not select: ', @lst_TableName_Not_Select, @NEW_LINE)
|
|
75
|
PRINT CONCAT('Skip table when selecting: ', STUFF(@lst_TableName_Fail,1,2,''))
|
|
76
|
--
|
|
77
|
CLOSE Detail
|
|
78
|
DEALLOCATE Detail
|
|
79
|
IF(@@TRANCOUNT > 0)
|
|
80
|
BEGIN
|
|
81
|
COMMIT TRANSACTION
|
|
82
|
END
|
|
83
|
END TRY
|
|
84
|
BEGIN CATCH
|
|
85
|
PRINT 'ERROR'
|
|
86
|
PRINT 'ERROR_MESSAGE: ' + ERROR_MESSAGE()
|
|
87
|
PRINT 'LINE: ' + CAST(ERROR_LINE() AS NVARCHAR)
|
|
88
|
--
|
|
89
|
IF(CURSOR_STATUS('global','Detail') >= -1)
|
|
90
|
BEGIN
|
|
91
|
IF(CURSOR_STATUS('global', 'Detail') > -1)
|
|
92
|
CLOSE Detail
|
|
93
|
DEALLOCATE Detail
|
|
94
|
END
|
|
95
|
IF(@@TRANCOUNT > 0)
|
|
96
|
BEGIN
|
|
97
|
ROLLBACK TRANSACTION
|
|
98
|
END
|
|
99
|
END CATCH
|