guynoniousg

temp

Sep 22nd, 2020
596
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --1
  2. CREATE TABLE dbo.CustomerPhones
  3. (
  4.   CustomerID INT PRIMARY KEY, -- FK
  5.   Phone1 VARCHAR(32),
  6.   Phone2 VARCHAR(32),
  7.   Phone3 VARCHAR(32)
  8. );
  9. INSERT dbo.CustomerPhones
  10.   (CustomerID, Phone1, Phone2, Phone3)
  11. VALUES
  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
  23. INTO #PhoneList  
  24. FROM INFORMATION_SCHEMA.COLUMNS
  25. WHERE TABLE_NAME = 'CustomerPhones'
  26.     AND COLUMN_NAME<> 'CustomerId'
  27.  
  28.  
  29. --DYNAMIC QURY TO RETRIVE ID AND NUMBER
  30. DECLARE @Query NVARCHAR(250) = ''
  31. DECLARE @ID INT=1
  32. WHILE EXISTS(SELECT 1 FROM #PhoneList)
  33. BEGIN
  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
  52. EXEC SP_EXECUTESQL @Query
  53.  
  54.  
  55.  
  56.  
  57. --2
  58.  
  59. --COLLECTING NAMEOF ALL TABLES IN DB
  60. DROP TABLE #tablelist
  61. SELECT
  62.     ID = IDENTITY(INT,1,1),
  63.     [name]
  64. INTO #TableList  
  65. FROM SYS.TABLES
  66.  
  67. DECLARE @Query NVARCHAR(250) = ''
  68. DECLARE @ID INT=1
  69. WHILE EXISTS(SELECT 1 FROM #TableList)
  70. BEGIN
  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
  87. DROP TABLE #tablelist
  88. SELECT
  89.     ID = IDENTITY(INT,1,1),
  90.     [name]
  91. INTO #TableList  
  92. FROM SYS.TABLES
  93.  
  94. DECLARE @Query NVARCHAR(250) = ''
  95. DECLARE @ID INT=1
  96. WHILE EXISTS(SELECT 1 FROM #TableList)
  97. BEGIN
  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
RAW Paste Data