/* Drop TABLES ---------------------------- */ IF OBJECT_ID('tempdb.dbo.#VariableCost', 'U') IS NOT NULL DROP TABLE # VARIABLECOST; GO IF OBJECT_ID('dbo.VariableCost', 'U') IS NOT NULL DROP TABLE VARIABLECOST; GO /* DELETE MULTIPLE TABLES ---------------------------- */ DECLARE @SQL NVARCHAR(MAX) = N''; SELECT @SQL += 'DROP TABLE ' + QUOTENAME(S.NAME) + '.' + QUOTENAME(T.NAME) + ';' + CHAR(10) FROM SYS.TABLES AS T INNER JOIN SYS.SCHEMAS AS S ON T.[SCHEMA_ID] = S.[SCHEMA_ID] WHERE T.NAME LIKE 'z_ps%'; PRINT @SQL EXEC SP_EXECUTESQL @SQL; /* COPY MULTIPLE TABLES TO OTHER DB ---------------------------- */ DECLARE @SQL NVARCHAR(MAX) = N''; SELECT @SQL += 'SELECT * into DummyDB.dbo.' + QUOTENAME(T.NAME) + ' FROM ' + QUOTENAME(T.NAME) + ';' + CHAR(10) FROM SYS.TABLES AS T INNER JOIN SYS.SCHEMAS AS S ON T.[SCHEMA_ID] = S.[SCHEMA_ID] WHERE T.NAME LIKE 'RAW_%' ; PRINT @SQL EXEC SP_EXECUTESQL @SQL; /* RENAME MULTIPLE TABLES ---------------------------- */ DECLARE @SQL NVARCHAR(MAX) = N''; SELECT @SQL += 'exec sp_rename ''' + QUOTENAME(T.NAME) + ''', ''STG_' + LEFT(RIGHT(QUOTENAME(T.NAME), LEN(QUOTENAME(T.NAME)) - 1), LEN(QUOTENAME(T.NAME)) - 6) + '''' + CHAR(10) FROM SYS.TABLES AS T INNER JOIN SYS.SCHEMAS AS S ON T.[SCHEMA_ID] = S.[SCHEMA_ID] WHERE T.NAME LIKE '%_STG'; PRINT @SQL EXEC SP_EXECUTESQL @SQL; /* IMPORT DATA FROM EXCEL FILE ---------------------------- */ SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\\Excel_Files\\REF_Dummy_excel_file.xlsx' , [SHEET1 $ ]) /* SUPER TABLE SPLITTER ---------------------------- */ SELECT DISTINCT TH_SOURCE, PARSENAME(REPLACE(S.A.VALUE('(/H/r)[1]', 'VARCHAR(100)'), '-', '.'), 1) AS TH1 INTO # THSPLITTER_SCN103 FROM ( SELECT *, CAST (N'<H><r>' + REPLACE(TH_SOURCE, '/', '</r><r>') + '</r></H>' AS XML) AS [VALS] FROM REF_REPORTING_SUMMARY_SCN103 ) D CROSS APPLY D.[VALS].NODES('/H/r') S(A) /* COMPARE TABLES FOR String will be truncated error while inserting into a table ---------------------------- */ SELECT T1.TABLE_NAME, T1.COLUMN_NAME, T1.CHARACTER_MAXIMUM_LENGTH, T2.CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS T1 INNER JOIN INFORMATION_SCHEMA.COLUMNS T2 ON (T1.COLUMN_NAME = T2.COLUMN_NAME) WHERE T1.TABLE_NAME = 'DATA_NEW_SH_WIDEOPEN_EORD_2018' AND T2.TABLE_NAME = 'z_ps_showo_dec13' AND ISNULL(T1.CHARACTER_MAXIMUM_LENGTH, 0) < ISNULL(T2.CHARACTER_MAXIMUM_LENGTH, 0) GO
For updated reference sheet checkout my github repo: https://github.com/pratyush272/REF_SQLScripts