Every once in a while, I have a data conversion project in which they want to move data to another server and change a number of legacy views into tables. This is most common when the views use data from linked servers and we have to migrate everything over to Azure SQL Database which doesn't allow cross database queries.
One could manually write the CREATE TABLE scripts for each, but that's a lot of work. We can leverage the system tables to help us create the scripts from the views.
Here's my rendition of the script:
SELECT CONCAT('CREATE TABLE [',s.name,'].[',v.name,']
(',STRING_AGG(CONCAT (
'[',c.name,'] ',t.name
,CASE WHEN t.name = 'varchar' and c.max_length = -1
THEN '(max)'
WHEN t.name IN ('varbinary','varchar')
THEN CONCAT ('(',convert(VARCHAR(50), c.max_length),')')
WHEN t.name IN ('decimal','numeric')
THEN CONCAT ('(',convert(VARCHAR(50), c.precision),',',convert(VARCHAR(50), c.scale),')')
WHEN t.name = 'datetime2'
THEN CONCAT ('(',convert(VARCHAR(50), c.scale),')')
ELSE ''
END
,' '
,CASE WHEN c.is_nullable = 1
THEN 'NULL'
ELSE 'NOT NULL'
END), ',') WITHIN GROUP (ORDER BY c.column_id),')
GO ') AS tablename
FROM sys.all_columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
JOIN sys.VIEWS v ON c.object_id = v.object_id
JOIN sys.schemas s ON v.schema_id = s.schema_id
WHERE v.name IN ('myView1','myView2') ---put your view names here
GROUP BY s.name,v.name
ORDER BY s.name,v.name
The advantage of my script is that it aggregates all of the text for each table in a single row in the results. The STRING_AGG gracefully handles skipping the comma after the last column, so nothing has to be deleted from the script when copying it to a query window to run, and the CREATE TABLE statement is formatted pretty close to they way most people format their table scripts. I also included the square brackets around the Schema Name, Table Name, and Column Name in case the legacy views used reserved words in any of those places.
I'm sure this will come in handy in the future since this is the second project where I've needed this script and I forgot to save it last time.
My company is hiring!!
