Is it possible to script a view as a table in SQL Server?
Under the "Script to" options you can choose to script as create to whatever you want. Is there an开发者_JAVA技巧y way to get a matching table structure for a view using a similar method?
Not out of the box. You can do SELECT INTO
from the View to create a new empty table then script that as a CREATE TABLE
using the usual SSMS methods.
SELECT TOP 0 * INTO NewTable FROM YourView
This might save a bit of typing if that's the motivation for the question.
Here's what we're using to do this.
All credit for the below goes to the top post here and @Zanlok whose revised version I've used and ammended to look at views rather than tables:
SELECT
t.TABLE_CATALOG,
t.TABLE_SCHEMA,
t.TABLE_NAME,
'create table '+QuoteName(t.TABLE_SCHEMA)+'.' + QuoteName(so.name) + ' (' + LEFT(o.List, Len(o.List)-1) + '); '
+ CASE WHEN tc.Constraint_Name IS NULL THEN ''
ELSE
'ALTER TABLE ' + QuoteName(t.TABLE_SCHEMA)+'.' + QuoteName(so.name)
+ ' ADD CONSTRAINT ' + tc.Constraint_Name + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + '); '
END as 'SQL_CREATE_TABLE'
FROM sysobjects so
CROSS APPLY (
SELECT
' ['+column_name+'] '
+ data_type
+ case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'ntext' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else
coalesce(
'('+ case when character_maximum_length = -1
then 'MAX'
else cast(character_maximum_length as varchar) end
+ ')','')
end
+ ' '
+ case when exists (
SELECT id
FROM syscolumns
WHERE
object_name(id) = so.name
and name = column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(so.name) as varchar) + ',' +
cast(ident_incr(so.name) as varchar) + ')'
else ''
end
+ ' '
+ (case when IS_NULLABLE = 'No' then 'NOT ' else '' end)
+ 'NULL '
+ case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT
ELSE ''
END
+ ',' -- can't have a field name or we'll end up with XML
FROM information_schema.columns
WHERE table_name = so.name
ORDER BY ordinal_position
FOR XML PATH('')
) o (list)
LEFT JOIN information_schema.table_constraints tc on
tc.Table_name = so.Name
AND tc.Constraint_Type = 'PRIMARY KEY'
LEFT JOIN information_schema.tables t on
t.Table_name = so.Name
CROSS APPLY (
SELECT QuoteName(Column_Name) + ', '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
) j (list)
WHERE
xtype = 'V'
AND name NOT IN ('dtproperties')
-- AND so.name = 'ASPStateTempSessions'
;
If you have it installed, you could use the SQL Server Import and Export Wizard. Set your source and destination to the same server, the source being the view, the destination being a new table. This will take the column definitions from the view and create a new table with those definitions.
The obvious downside is that the new table will have the data from the old view, which might be an issue if you have a large number of rows as a result of the view. The easiest way around this and to create a blank table with the definition of the view is to select the option "Write a query to specify the data to transfer" in the wizare and use some code like this:
SELECT * FROM YourView WHERE 1=2
Since 1 does not equal 2, no rows will be returned, but the column definition will be retained, and the new table will be created with that definition.
Hope that helps!
If you mean generate a CREATE TABLE
statement based on the columns selected in a View's definition, I'm not aware of any way to do that without intermediate steps, such as what @Martin suggests.
If you have access to SSIS, insert a dataflow. For source, use your view. For destination, use the new table. The entire table will be generated with column and datatypes. If you don't want to transfer data, you can leave it as is. Your table would have already been created when you click Yes to save your dataflow. Now, you can go to the SSMS and generate a script if you wish to see the column names and datatypes for the new table you just generated.
精彩评论