October 2, 2012 at 7:07 pm
I would take a different approach. Not necessarily better, but different.
I would avoid the cursor and the immediate EXEC(), instead just use a query to produce the desired script for all tables (Output to Text). It's easy to copy the results to a new window and possibly make some manual corrections.
Instead of building the script with a lot of concatenation, I like to wrap a readable script template in as many REPLACE() functions as necessary.
I don't like the SELECT * definitions, it's not hard to get the column list.
If I had to exclude columns like SSN that should not be exposed, I would add an extended property to those columns to keep them out of the column list. These exclusions will then be repeated if the script has to be run again in future.
SELECTREPLACE(REPLACE(REPLACE(
'CREATE VIEW [<s>].vw_[<t>] AS
SELECT <list>
FROM [<s>].[<t>]
GO',
'<t>', tbl.name),
'<s>', sch.name),
'<list>', STUFF(cl.ColumnList, 1, 2, ''))
FROM sys.tables tbl
INNER JOIN sys.schemas sch ON tbl.schema_id = sch.schema_id
CROSS APPLY (
SELECT', ' + QUOTENAME(col.name)
FROM sys.columns col
LEFT JOIN sys.extended_properties xp ON xp.major_id = col.object_id AND xp.minor_id = col.column_id
AND xp.class = 1 AND xp.name = 'HiddenColumn'
WHERE col.object_id = tbl.object_id AND xp.major_id IS NULL
ORDER BY column_id
FOR XML PATH('')
) cl (ColumnList)
WHERE tbl.is_ms_shipped = 0 AND cl.ColumnList IS NOT NULL
AND NOT EXISTS(SELECT NULL FROM sys.objects WHERE name = 'vw_' + tbl.name AND schema_id = tbl.schema_id)
October 3, 2012 at 11:40 am
Scott Coleman (10/2/2012)
I would take a different approach. Not necessarily better, but different.I would avoid the cursor and the immediate EXEC(), instead just use a query to produce the desired script for all tables (Output to Text). It's easy to copy the results to a new window and possibly make some manual corrections.
Instead of building the script with a lot of concatenation, I like to wrap a readable script template in as many REPLACE() functions as necessary.
I don't like the SELECT * definitions, it's not hard to get the column list.
If I had to exclude columns like SSN that should not be exposed, I would add an extended property to those columns to keep them out of the column list. These exclusions will then be repeated if the script has to be run again in future.
SELECTREPLACE(REPLACE(REPLACE('CREATE VIEW [<s>].vw_[<t>] AS
SELECT <list>
FROM [<s>].[<t>]
GO',
'<t>', tbl.name),
'<s>', sch.name),
'<list>', STUFF(cl.ColumnList, 1, 2, ''))
FROM sys.tables tbl
INNER JOIN sys.schemas sch ON tbl.schema_id = sch.schema_id
CROSS APPLY (
SELECT', ' + QUOTENAME(col.name)
FROM sys.columns col
LEFT JOIN sys.extended_properties xp ON xp.major_id = col.object_id AND xp.minor_id = col.column_id
AND xp.class = 1 AND xp.name = 'HiddenColumn'
WHERE col.object_id = tbl.object_id AND xp.major_id IS NULL
ORDER BY column_id
FOR XML PATH('')
) cl (ColumnList)
WHERE tbl.is_ms_shipped = 0 AND cl.ColumnList IS NOT NULL
AND NOT EXISTS(SELECT NULL FROM sys.objects WHERE name = 'vw_' + tbl.name AND schema_id = tbl.schema_id)
Nice! I like variations. This may seem a bit advanced for the beginners, but, they will be alright with multiple options.
thx for the post Scott
Cheers,
John Esraelo
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply