る 貉朱 豕 500蟾讌 讌.
'PvP' 螳 貉朱 讒..
create table test2(AccountId int, UserName varchar(20))
go
declare
@table_name nvarchar(500)
set @table_name = 'test2'
select
ordinal_position
, column_name
, trans_column_name
, concat(case when ordinal_position = 1 then ' ' else ', ' end, column_name, ' ', replicate(' ', max(len(column_name)) over() - len(column_name)), trans_column_name) sql
, concat(case when ordinal_position = 1 then ' a.' else ', a.' end, trans_column_name, ' ', replicate(' ', max(len(trans_column_name)) over() - len(trans_column_name)), ' = b.', trans_column_name) sql
, concat('exec sp_rename ''', @table_name, '.', column_name, ''', ''', trans_column_name, '''', ', ''COLUMN''') sql
from (
select
column_name
, ordinal_position
, max(iif(seq = 1, letter, '')) +
max(iif(seq = 2, letter, '')) +
max(iif(seq = 3, letter, '')) +
max(iif(seq = 4, letter, '')) +
max(iif(seq = 5, letter, '')) +
max(iif(seq = 6, letter, '')) +
max(iif(seq = 7, letter, '')) +
max(iif(seq = 8, letter, '')) +
max(iif(seq = 9, letter, '')) +
max(iif(seq = 10, letter, '')) +
max(iif(seq = 11, letter, '')) +
max(iif(seq = 12, letter, '')) +
max(iif(seq = 13, letter, '')) +
max(iif(seq = 14, letter, '')) +
max(iif(seq = 15, letter, '')) +
max(iif(seq = 16, letter, '')) +
max(iif(seq = 17, letter, '')) +
max(iif(seq = 18, letter, '')) +
max(iif(seq = 19, letter, '')) +
max(iif(seq = 20, letter, '')) +
max(iif(seq = 21, letter, '')) +
max(iif(seq = 22, letter, '')) +
max(iif(seq = 23, letter, '')) +
max(iif(seq = 24, letter, '')) +
max(iif(seq = 25, letter, '')) +
max(iif(seq = 26, letter, '')) +
max(iif(seq = 27, letter, '')) +
max(iif(seq = 28, letter, '')) +
max(iif(seq = 29, letter, '')) +
max(iif(seq = 30, letter, '')) +
max(iif(seq = 31, letter, '')) +
max(iif(seq = 32, letter, '')) +
max(iif(seq = 33, letter, '')) +
max(iif(seq = 34, letter, '')) +
max(iif(seq = 35, letter, '')) +
max(iif(seq = 36, letter, '')) +
max(iif(seq = 37, letter, '')) +
max(iif(seq = 38, letter, '')) +
max(iif(seq = 39, letter, '')) +
max(iif(seq = 40, letter, '')) +
max(iif(seq = 41, letter, '')) +
max(iif(seq = 42, letter, '')) +
max(iif(seq = 43, letter, '')) +
max(iif(seq = 44, letter, '')) +
max(iif(seq = 45, letter, '')) +
max(iif(seq = 46, letter, '')) +
max(iif(seq = 47, letter, '')) +
max(iif(seq = 48, letter, '')) +
max(iif(seq = 49, letter, '')) +
max(iif(seq = 50, letter, '')) +
max(iif(seq = 51, letter, '')) +
max(iif(seq = 52, letter, '')) +
max(iif(seq = 53, letter, '')) +
max(iif(seq = 54, letter, '')) +
max(iif(seq = 55, letter, '')) +
max(iif(seq = 56, letter, '')) +
max(iif(seq = 57, letter, '')) +
max(iif(seq = 58, letter, '')) +
max(iif(seq = 59, letter, '')) +
max(iif(seq = 60, letter, '')) +
max(iif(seq = 61, letter, '')) +
max(iif(seq = 62, letter, '')) +
max(iif(seq = 63, letter, '')) +
max(iif(seq = 64, letter, '')) +
max(iif(seq = 65, letter, '')) +
max(iif(seq = 66, letter, '')) +
max(iif(seq = 67, letter, '')) +
max(iif(seq = 68, letter, '')) +
max(iif(seq = 69, letter, '')) +
max(iif(seq = 70, letter, '')) +
max(iif(seq = 71, letter, '')) +
max(iif(seq = 72, letter, '')) +
max(iif(seq = 73, letter, '')) +
max(iif(seq = 74, letter, '')) +
max(iif(seq = 75, letter, '')) +
max(iif(seq = 76, letter, '')) +
max(iif(seq = 77, letter, '')) +
max(iif(seq = 78, letter, '')) +
max(iif(seq = 79, letter, '')) +
max(iif(seq = 80, letter, '')) +
max(iif(seq = 81, letter, '')) +
max(iif(seq = 82, letter, '')) +
max(iif(seq = 83, letter, '')) +
max(iif(seq = 84, letter, '')) +
max(iif(seq = 85, letter, '')) +
max(iif(seq = 86, letter, '')) +
max(iif(seq = 87, letter, '')) +
max(iif(seq = 88, letter, '')) +
max(iif(seq = 89, letter, '')) +
max(iif(seq = 90, letter, '')) +
max(iif(seq = 91, letter, '')) +
max(iif(seq = 92, letter, '')) +
max(iif(seq = 93, letter, '')) +
max(iif(seq = 94, letter, '')) +
max(iif(seq = 95, letter, '')) +
max(iif(seq = 96, letter, '')) +
max(iif(seq = 97, letter, '')) +
max(iif(seq = 98, letter, '')) +
max(iif(seq = 99, letter, '')) +
max(iif(seq = 100, letter, '')) +
max(iif(seq = 101, letter, '')) +
max(iif(seq = 102, letter, '')) +
max(iif(seq = 103, letter, '')) +
max(iif(seq = 104, letter, '')) +
max(iif(seq = 105, letter, '')) +
max(iif(seq = 106, letter, '')) +
max(iif(seq = 107, letter, '')) +
max(iif(seq = 108, letter, '')) +
max(iif(seq = 109, letter, '')) +
max(iif(seq = 110, letter, '')) +
max(iif(seq = 111, letter, '')) +
max(iif(seq = 112, letter, '')) +
max(iif(seq = 113, letter, '')) +
max(iif(seq = 114, letter, '')) +
max(iif(seq = 115, letter, '')) +
max(iif(seq = 116, letter, '')) +
max(iif(seq = 117, letter, '')) +
max(iif(seq = 118, letter, '')) +
max(iif(seq = 119, letter, '')) +
max(iif(seq = 120, letter, '')) +
max(iif(seq = 121, letter, '')) +
max(iif(seq = 122, letter, '')) +
max(iif(seq = 123, letter, '')) +
max(iif(seq = 124, letter, '')) +
max(iif(seq = 125, letter, '')) +
max(iif(seq = 126, letter, '')) +
max(iif(seq = 127, letter, '')) +
max(iif(seq = 128, letter, '')) +
max(iif(seq = 129, letter, '')) +
max(iif(seq = 130, letter, '')) +
max(iif(seq = 131, letter, '')) +
max(iif(seq = 132, letter, '')) +
max(iif(seq = 133, letter, '')) +
max(iif(seq = 134, letter, '')) +
max(iif(seq = 135, letter, '')) +
max(iif(seq = 136, letter, '')) +
max(iif(seq = 137, letter, '')) +
max(iif(seq = 138, letter, '')) +
max(iif(seq = 139, letter, '')) +
max(iif(seq = 140, letter, '')) +
max(iif(seq = 141, letter, '')) +
max(iif(seq = 142, letter, '')) +
max(iif(seq = 143, letter, '')) +
max(iif(seq = 144, letter, '')) +
max(iif(seq = 145, letter, '')) +
max(iif(seq = 146, letter, '')) +
max(iif(seq = 147, letter, '')) +
max(iif(seq = 148, letter, '')) +
max(iif(seq = 149, letter, '')) +
max(iif(seq = 150, letter, '')) +
max(iif(seq = 151, letter, '')) +
max(iif(seq = 152, letter, '')) +
max(iif(seq = 153, letter, '')) +
max(iif(seq = 154, letter, '')) +
max(iif(seq = 155, letter, '')) +
max(iif(seq = 156, letter, '')) +
max(iif(seq = 157, letter, '')) +
max(iif(seq = 158, letter, '')) +
max(iif(seq = 159, letter, '')) +
max(iif(seq = 160, letter, '')) +
max(iif(seq = 161, letter, '')) +
max(iif(seq = 162, letter, '')) +
max(iif(seq = 163, letter, '')) +
max(iif(seq = 164, letter, '')) +
max(iif(seq = 165, letter, '')) +
max(iif(seq = 166, letter, '')) +
max(iif(seq = 167, letter, '')) +
max(iif(seq = 168, letter, '')) +
max(iif(seq = 169, letter, '')) +
max(iif(seq = 170, letter, '')) +
max(iif(seq = 171, letter, '')) +
max(iif(seq = 172, letter, '')) +
max(iif(seq = 173, letter, '')) +
max(iif(seq = 174, letter, '')) +
max(iif(seq = 175, letter, '')) +
max(iif(seq = 176, letter, '')) +
max(iif(seq = 177, letter, '')) +
max(iif(seq = 178, letter, '')) +
max(iif(seq = 179, letter, '')) +
max(iif(seq = 180, letter, '')) +
max(iif(seq = 181, letter, '')) +
max(iif(seq = 182, letter, '')) +
max(iif(seq = 183, letter, '')) +
max(iif(seq = 184, letter, '')) +
max(iif(seq = 185, letter, '')) +
max(iif(seq = 186, letter, '')) +
max(iif(seq = 187, letter, '')) +
max(iif(seq = 188, letter, '')) +
max(iif(seq = 189, letter, '')) +
max(iif(seq = 190, letter, '')) +
max(iif(seq = 191, letter, '')) +
max(iif(seq = 192, letter, '')) +
max(iif(seq = 193, letter, '')) +
max(iif(seq = 194, letter, '')) +
max(iif(seq = 195, letter, '')) +
max(iif(seq = 196, letter, '')) +
max(iif(seq = 197, letter, '')) +
max(iif(seq = 198, letter, '')) +
max(iif(seq = 199, letter, '')) +
max(iif(seq = 200, letter, '')) +
max(iif(seq = 201, letter, '')) +
max(iif(seq = 202, letter, '')) +
max(iif(seq = 203, letter, '')) +
max(iif(seq = 204, letter, '')) +
max(iif(seq = 205, letter, '')) +
max(iif(seq = 206, letter, '')) +
max(iif(seq = 207, letter, '')) +
max(iif(seq = 208, letter, '')) +
max(iif(seq = 209, letter, '')) +
max(iif(seq = 210, letter, '')) +
max(iif(seq = 211, letter, '')) +
max(iif(seq = 212, letter, '')) +
max(iif(seq = 213, letter, '')) +
max(iif(seq = 214, letter, '')) +
max(iif(seq = 215, letter, '')) +
max(iif(seq = 216, letter, '')) +
max(iif(seq = 217, letter, '')) +
max(iif(seq = 218, letter, '')) +
max(iif(seq = 219, letter, '')) +
max(iif(seq = 220, letter, '')) +
max(iif(seq = 221, letter, '')) +
max(iif(seq = 222, letter, '')) +
max(iif(seq = 223, letter, '')) +
max(iif(seq = 224, letter, '')) +
max(iif(seq = 225, letter, '')) +
max(iif(seq = 226, letter, '')) +
max(iif(seq = 227, letter, '')) +
max(iif(seq = 228, letter, '')) +
max(iif(seq = 229, letter, '')) +
max(iif(seq = 230, letter, '')) +
max(iif(seq = 231, letter, '')) +
max(iif(seq = 232, letter, '')) +
max(iif(seq = 233, letter, '')) +
max(iif(seq = 234, letter, '')) +
max(iif(seq = 235, letter, '')) +
max(iif(seq = 236, letter, '')) +
max(iif(seq = 237, letter, '')) +
max(iif(seq = 238, letter, '')) +
max(iif(seq = 239, letter, '')) +
max(iif(seq = 240, letter, '')) +
max(iif(seq = 241, letter, '')) +
max(iif(seq = 242, letter, '')) +
max(iif(seq = 243, letter, '')) +
max(iif(seq = 244, letter, '')) +
max(iif(seq = 245, letter, '')) +
max(iif(seq = 246, letter, '')) +
max(iif(seq = 247, letter, '')) +
max(iif(seq = 248, letter, '')) +
max(iif(seq = 249, letter, '')) +
max(iif(seq = 250, letter, '')) +
max(iif(seq = 251, letter, '')) +
max(iif(seq = 252, letter, '')) +
max(iif(seq = 253, letter, '')) +
max(iif(seq = 254, letter, '')) +
max(iif(seq = 255, letter, '')) +
max(iif(seq = 256, letter, '')) +
max(iif(seq = 257, letter, '')) +
max(iif(seq = 258, letter, '')) +
max(iif(seq = 259, letter, '')) +
max(iif(seq = 260, letter, '')) +
max(iif(seq = 261, letter, '')) +
max(iif(seq = 262, letter, '')) +
max(iif(seq = 263, letter, '')) +
max(iif(seq = 264, letter, '')) +
max(iif(seq = 265, letter, '')) +
max(iif(seq = 266, letter, '')) +
max(iif(seq = 267, letter, '')) +
max(iif(seq = 268, letter, '')) +
max(iif(seq = 269, letter, '')) +
max(iif(seq = 270, letter, '')) +
max(iif(seq = 271, letter, '')) +
max(iif(seq = 272, letter, '')) +
max(iif(seq = 273, letter, '')) +
max(iif(seq = 274, letter, '')) +
max(iif(seq = 275, letter, '')) +
max(iif(seq = 276, letter, '')) +
max(iif(seq = 277, letter, '')) +
max(iif(seq = 278, letter, '')) +
max(iif(seq = 279, letter, '')) +
max(iif(seq = 280, letter, '')) +
max(iif(seq = 281, letter, '')) +
max(iif(seq = 282, letter, '')) +
max(iif(seq = 283, letter, '')) +
max(iif(seq = 284, letter, '')) +
max(iif(seq = 285, letter, '')) +
max(iif(seq = 286, letter, '')) +
max(iif(seq = 287, letter, '')) +
max(iif(seq = 288, letter, '')) +
max(iif(seq = 289, letter, '')) +
max(iif(seq = 290, letter, '')) +
max(iif(seq = 291, letter, '')) +
max(iif(seq = 292, letter, '')) +
max(iif(seq = 293, letter, '')) +
max(iif(seq = 294, letter, '')) +
max(iif(seq = 295, letter, '')) +
max(iif(seq = 296, letter, '')) +
max(iif(seq = 297, letter, '')) +
max(iif(seq = 298, letter, '')) +
max(iif(seq = 299, letter, '')) +
max(iif(seq = 300, letter, '')) +
max(iif(seq = 301, letter, '')) +
max(iif(seq = 302, letter, '')) +
max(iif(seq = 303, letter, '')) +
max(iif(seq = 304, letter, '')) +
max(iif(seq = 305, letter, '')) +
max(iif(seq = 306, letter, '')) +
max(iif(seq = 307, letter, '')) +
max(iif(seq = 308, letter, '')) +
max(iif(seq = 309, letter, '')) +
max(iif(seq = 310, letter, '')) +
max(iif(seq = 311, letter, '')) +
max(iif(seq = 312, letter, '')) +
max(iif(seq = 313, letter, '')) +
max(iif(seq = 314, letter, '')) +
max(iif(seq = 315, letter, '')) +
max(iif(seq = 316, letter, '')) +
max(iif(seq = 317, letter, '')) +
max(iif(seq = 318, letter, '')) +
max(iif(seq = 319, letter, '')) +
max(iif(seq = 320, letter, '')) +
max(iif(seq = 321, letter, '')) +
max(iif(seq = 322, letter, '')) +
max(iif(seq = 323, letter, '')) +
max(iif(seq = 324, letter, '')) +
max(iif(seq = 325, letter, '')) +
max(iif(seq = 326, letter, '')) +
max(iif(seq = 327, letter, '')) +
max(iif(seq = 328, letter, '')) +
max(iif(seq = 329, letter, '')) +
max(iif(seq = 330, letter, '')) +
max(iif(seq = 331, letter, '')) +
max(iif(seq = 332, letter, '')) +
max(iif(seq = 333, letter, '')) +
max(iif(seq = 334, letter, '')) +
max(iif(seq = 335, letter, '')) +
max(iif(seq = 336, letter, '')) +
max(iif(seq = 337, letter, '')) +
max(iif(seq = 338, letter, '')) +
max(iif(seq = 339, letter, '')) +
max(iif(seq = 340, letter, '')) +
max(iif(seq = 341, letter, '')) +
max(iif(seq = 342, letter, '')) +
max(iif(seq = 343, letter, '')) +
max(iif(seq = 344, letter, '')) +
max(iif(seq = 345, letter, '')) +
max(iif(seq = 346, letter, '')) +
max(iif(seq = 347, letter, '')) +
max(iif(seq = 348, letter, '')) +
max(iif(seq = 349, letter, '')) +
max(iif(seq = 350, letter, '')) +
max(iif(seq = 351, letter, '')) +
max(iif(seq = 352, letter, '')) +
max(iif(seq = 353, letter, '')) +
max(iif(seq = 354, letter, '')) +
max(iif(seq = 355, letter, '')) +
max(iif(seq = 356, letter, '')) +
max(iif(seq = 357, letter, '')) +
max(iif(seq = 358, letter, '')) +
max(iif(seq = 359, letter, '')) +
max(iif(seq = 360, letter, '')) +
max(iif(seq = 361, letter, '')) +
max(iif(seq = 362, letter, '')) +
max(iif(seq = 363, letter, '')) +
max(iif(seq = 364, letter, '')) +
max(iif(seq = 365, letter, '')) +
max(iif(seq = 366, letter, '')) +
max(iif(seq = 367, letter, '')) +
max(iif(seq = 368, letter, '')) +
max(iif(seq = 369, letter, '')) +
max(iif(seq = 370, letter, '')) +
max(iif(seq = 371, letter, '')) +
max(iif(seq = 372, letter, '')) +
max(iif(seq = 373, letter, '')) +
max(iif(seq = 374, letter, '')) +
max(iif(seq = 375, letter, '')) +
max(iif(seq = 376, letter, '')) +
max(iif(seq = 377, letter, '')) +
max(iif(seq = 378, letter, '')) +
max(iif(seq = 379, letter, '')) +
max(iif(seq = 380, letter, '')) +
max(iif(seq = 381, letter, '')) +
max(iif(seq = 382, letter, '')) +
max(iif(seq = 383, letter, '')) +
max(iif(seq = 384, letter, '')) +
max(iif(seq = 385, letter, '')) +
max(iif(seq = 386, letter, '')) +
max(iif(seq = 387, letter, '')) +
max(iif(seq = 388, letter, '')) +
max(iif(seq = 389, letter, '')) +
max(iif(seq = 390, letter, '')) +
max(iif(seq = 391, letter, '')) +
max(iif(seq = 392, letter, '')) +
max(iif(seq = 393, letter, '')) +
max(iif(seq = 394, letter, '')) +
max(iif(seq = 395, letter, '')) +
max(iif(seq = 396, letter, '')) +
max(iif(seq = 397, letter, '')) +
max(iif(seq = 398, letter, '')) +
max(iif(seq = 399, letter, '')) +
max(iif(seq = 400, letter, '')) +
max(iif(seq = 401, letter, '')) +
max(iif(seq = 402, letter, '')) +
max(iif(seq = 403, letter, '')) +
max(iif(seq = 404, letter, '')) +
max(iif(seq = 405, letter, '')) +
max(iif(seq = 406, letter, '')) +
max(iif(seq = 407, letter, '')) +
max(iif(seq = 408, letter, '')) +
max(iif(seq = 409, letter, '')) +
max(iif(seq = 410, letter, '')) +
max(iif(seq = 411, letter, '')) +
max(iif(seq = 412, letter, '')) +
max(iif(seq = 413, letter, '')) +
max(iif(seq = 414, letter, '')) +
max(iif(seq = 415, letter, '')) +
max(iif(seq = 416, letter, '')) +
max(iif(seq = 417, letter, '')) +
max(iif(seq = 418, letter, '')) +
max(iif(seq = 419, letter, '')) +
max(iif(seq = 420, letter, '')) +
max(iif(seq = 421, letter, '')) +
max(iif(seq = 422, letter, '')) +
max(iif(seq = 423, letter, '')) +
max(iif(seq = 424, letter, '')) +
max(iif(seq = 425, letter, '')) +
max(iif(seq = 426, letter, '')) +
max(iif(seq = 427, letter, '')) +
max(iif(seq = 428, letter, '')) +
max(iif(seq = 429, letter, '')) +
max(iif(seq = 430, letter, '')) +
max(iif(seq = 431, letter, '')) +
max(iif(seq = 432, letter, '')) +
max(iif(seq = 433, letter, '')) +
max(iif(seq = 434, letter, '')) +
max(iif(seq = 435, letter, '')) +
max(iif(seq = 436, letter, '')) +
max(iif(seq = 437, letter, '')) +
max(iif(seq = 438, letter, '')) +
max(iif(seq = 439, letter, '')) +
max(iif(seq = 440, letter, '')) +
max(iif(seq = 441, letter, '')) +
max(iif(seq = 442, letter, '')) +
max(iif(seq = 443, letter, '')) +
max(iif(seq = 444, letter, '')) +
max(iif(seq = 445, letter, '')) +
max(iif(seq = 446, letter, '')) +
max(iif(seq = 447, letter, '')) +
max(iif(seq = 448, letter, '')) +
max(iif(seq = 449, letter, '')) +
max(iif(seq = 450, letter, '')) +
max(iif(seq = 451, letter, '')) +
max(iif(seq = 452, letter, '')) +
max(iif(seq = 453, letter, '')) +
max(iif(seq = 454, letter, '')) +
max(iif(seq = 455, letter, '')) +
max(iif(seq = 456, letter, '')) +
max(iif(seq = 457, letter, '')) +
max(iif(seq = 458, letter, '')) +
max(iif(seq = 459, letter, '')) +
max(iif(seq = 460, letter, '')) +
max(iif(seq = 461, letter, '')) +
max(iif(seq = 462, letter, '')) +
max(iif(seq = 463, letter, '')) +
max(iif(seq = 464, letter, '')) +
max(iif(seq = 465, letter, '')) +
max(iif(seq = 466, letter, '')) +
max(iif(seq = 467, letter, '')) +
max(iif(seq = 468, letter, '')) +
max(iif(seq = 469, letter, '')) +
max(iif(seq = 470, letter, '')) +
max(iif(seq = 471, letter, '')) +
max(iif(seq = 472, letter, '')) +
max(iif(seq = 473, letter, '')) +
max(iif(seq = 474, letter, '')) +
max(iif(seq = 475, letter, '')) +
max(iif(seq = 476, letter, '')) +
max(iif(seq = 477, letter, '')) +
max(iif(seq = 478, letter, '')) +
max(iif(seq = 479, letter, '')) +
max(iif(seq = 480, letter, '')) +
max(iif(seq = 481, letter, '')) +
max(iif(seq = 482, letter, '')) +
max(iif(seq = 483, letter, '')) +
max(iif(seq = 484, letter, '')) +
max(iif(seq = 485, letter, '')) +
max(iif(seq = 486, letter, '')) +
max(iif(seq = 487, letter, '')) +
max(iif(seq = 488, letter, '')) +
max(iif(seq = 489, letter, '')) +
max(iif(seq = 490, letter, '')) +
max(iif(seq = 491, letter, '')) +
max(iif(seq = 492, letter, '')) +
max(iif(seq = 493, letter, '')) +
max(iif(seq = 494, letter, '')) +
max(iif(seq = 495, letter, '')) +
max(iif(seq = 496, letter, '')) +
max(iif(seq = 497, letter, '')) +
max(iif(seq = 498, letter, '')) +
max(iif(seq = 499, letter, '')) +
max(iif(seq = 500, letter, '')) trans_column_name
, max(seq) max_seq
from (
select
column_name
, ordinal_position
, case
when substring(a.column_name, b.seq, 1) collate korean_wansung_cs_as like '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]' and b.seq > 1 then '_' + lower(substring(a.column_name, b.seq, 1))
when substring(a.column_name, b.seq, 1) collate korean_wansung_cs_as like '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]' then lower(substring(a.column_name, b.seq, 1))
else substring(a.column_name, b.seq, 1)
end letter
, seq
from information_schema.columns a
cross join dw.dim.dummy b
where 1=1
and a.table_name = @table_name
and a.table_schema = 'dbo'
and b.seq <= len(a.column_name)
) t
group by
column_name
, ordinal_position
) t
order by ordinal_position