· 5 years ago · Sep 22, 2020, 11:28 AM
1--1
2CREATE TABLE dbo.CustomerPhones
3(
4 CustomerID INT PRIMARY KEY, -- FK
5 Phone1 VARCHAR(32),
6 Phone2 VARCHAR(32),
7 Phone3 VARCHAR(32)
8);
9INSERT dbo.CustomerPhones
10 (CustomerID, Phone1, Phone2, Phone3)
11VALUES
12 (1,'705-491-1111', '705-491-1110', NULL),
13 (2,'613-492-2222', NULL, NULL),
14 (3,'416-493-3333', '416-493-3330', '416-493-3339');
15 DROP TABLE #PhoneList
16
17
18-- Creating a temp table to store the column names of phone
19 DROP TABLE IF EXISTS #PhoneList
20 SELECT
21 ID = IDENTITY(INT,1,1),
22 COLUMN_NAME
23INTO #PhoneList
24FROM INFORMATION_SCHEMA.COLUMNS
25WHERE TABLE_NAME = 'CustomerPhones'
26 AND COLUMN_NAME<> 'CustomerId'
27
28
29--DYNAMIC QURY TO RETRIVE ID AND NUMBER
30DECLARE @Query NVARCHAR(250) = ''
31DECLARE @ID INT=1
32WHILE EXISTS(SELECT 1 FROM #PhoneList)
33BEGIN
34 IF @Query=''
35
36 SELECT @Query = 'SELECT CustomerID, '+COLUMN_NAME+' FROM CustomerPhones WHERE '+COLUMN_NAME+ ' IS NOT NULL'
37 FROM #PhoneList
38 WHERE @ID=ID
39 ELSE
40 SELECT @Query = @query +' UNION SELECT CustomerID, '+COLUMN_NAME+' FROM CustomerPhones WHERE '+COLUMN_NAME+ ' IS NOT NULL'
41 FROM #PhoneList
42 WHERE @ID=ID
43
44 --PRINT @Query
45
46
47 DELETE FROM #PhoneList
48 WHERE ID=@ID
49 SET @ID=@ID+1
50
51 END
52EXEC SP_EXECUTESQL @Query
53
54
55
56
57--2
58
59--COLLECTING NAMEOF ALL TABLES IN DB
60DROP TABLE #tablelist
61SELECT
62 ID = IDENTITY(INT,1,1),
63 [name]
64INTO #TableList
65FROM SYS.tables
66
67DECLARE @Query NVARCHAR(250) = ''
68DECLARE @ID INT=1
69WHILE EXISTS(SELECT 1 FROM #TableList)
70BEGIN
71 SELECT @Query = 'ALTER TABLE ' + [name] + ' ADD TableName varchar(max)'
72 FROM #TableList
73 WHERE @ID=ID
74
75 PRINT @Query
76 --EXEC SP_EXECUTESQL @Query
77
78 DELETE FROM #TableList
79 WHERE ID=@ID
80 SET @ID=@ID+1
81 END
82
83
84
85--3
86--COLLECTING NAMEOF ALL TABLES IN DB
87DROP TABLE #tablelist
88SELECT
89 ID = IDENTITY(INT,1,1),
90 [name]
91INTO #TableList
92FROM SYS.tables
93
94DECLARE @Query NVARCHAR(250) = ''
95DECLARE @ID INT=1
96WHILE EXISTS(SELECT 1 FROM #TableList)
97BEGIN
98 SELECT @Query = 'UPDATE ' + [name] + ' SET TableName = ' + [name]
99 FROM #TableList
100 WHERE @ID=ID
101
102 PRINT @Query
103 --EXEC SP_EXECUTESQL @Query
104
105 DELETE FROM #TableList
106 WHERE ID=@ID
107 SET @ID=@ID+1
108 END