May 11, 2011 at 2:37 pm
I have a collection of large views (many columns) and want to materialize these as tables in a second database. Is there a way to use T-SQL to produce scripts?
May 11, 2011 at 3:10 pm
The quick solution is to use select..into to create tables. Then you have to script the tables and adjust constraints and nullability.
Regards
Piotr
...and your only reply is slàinte mhath
May 12, 2011 at 1:25 pm
Yes, that would be a partial solution, especially since 90-95% of columns are nullable..
May 13, 2011 at 5:21 am
You could also try to use this code. It should generate column definitions for your views. copy/paste them from results and wrap into CREATE TABLE statement.
select column_name + ' ' + Data_type +
case when character_maximum_length is null then
case when data_type in ('decimal', 'numeric')
then ' (' + convert(varchar, numeric_precision) + ', ' + convert(varchar, numeric_scale) + ')'
else ''
end
else
case when data_type not in('text', 'image' ,'ntext') then
'(' + replace(convert(varchar, character_maximum_length), '-1', 'max') + ')'
else ''
end
end +
case when is_nullable = 'YES' then ' NULL' else ' NOT NULL' end
+ ','
from information_schema.columns where table_name = 'yourviewname'
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply