基于源数据库视图结构生成SQL Server建表脚本的实现方案

Absolutely! You can definitely generate CREATE TABLE scripts based on your view structures using T-SQL code—this is a go-to workaround when GUI-based methods fail due to permissions or limitations. Here are two reliable approaches you can use right away:

Method 1: Build Scripts Using System Catalog Views #

This approach directly queries SQL Server's built-in system tables to extract column metadata (names, data types, nullability, etc.) from your views, then constructs a complete CREATE TABLE statement.

DECLARE @ViewName NVARCHAR(128) = N'YourViewName'; -- Replace with your actual view name
DECLARE @SchemaName NVARCHAR(128) = N'dbo'; -- Replace with your view's schema

SELECT 
    'CREATE TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ViewName + '_Table') + ' (' + 
    STRING_AGG(
        QUOTENAME(c.name) + ' ' + 
        t.name + 
        CASE WHEN t.name IN ('varchar', 'nvarchar', 'char', 'nchar') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END + ')' ELSE '' END + 
        CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END,
        ', '
    ) WITHIN GROUP (ORDER BY c.column_id) + 
    ');' AS CreateTableScript
FROM sys.views v
JOIN sys.columns c ON v.object_id = c.object_id
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE v.name = @ViewName AND SCHEMA_NAME(v.schema_id) = @SchemaName;

Notes for this method: #

• The script creates a table named [YourViewName]_Table (you can adjust the naming logic in the QUOTENAME part if needed).
• It preserves core column properties: data type, length (including MAX for variable-length types), and nullability.
• For views with computed columns, you may need to tweak the logic to map the computed result to the correct base data type.

Method 2: Use the sys.dm_exec_describe_first_result_set DMF #

This dynamic management function analyzes the view's actual result set and returns detailed metadata about each column. It’s especially useful for complex views with joins, computed columns, or subqueries where system catalog views might not capture the exact output structure.

DECLARE @ViewName NVARCHAR(128) = N'YourViewName';
DECLARE @SchemaName NVARCHAR(128) = N'dbo';
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ViewName);

SELECT 
    'CREATE TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ViewName + '_Table') + ' (' + 
    STRING_AGG(
        QUOTENAME(name) + ' ' + 
        system_type_name + 
        CASE WHEN is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END,
        ', '
    ) WITHIN GROUP (ORDER BY column_ordinal) + 
    ');' AS CreateTableScript
FROM sys.dm_exec_describe_first_result_set(@SQL, NULL, 0);

Notes for this method: #

• The system_type_name column directly gives you the full data type definition (e.g., nvarchar(50) instead of just nvarchar), so you don’t have to build it manually.
• This works even if the view includes derived columns or uses functions that alter data types.

General Tips #

• Replace @ViewName and @SchemaName with your actual view details before running the scripts.
• If you need to include additional constraints (like primary keys, defaults, or indexes), you’ll have to extend the scripts or add those manually after generating the base table.
• Permission-wise: Both methods require basic SELECT access to system catalog views (sys.views, sys.columns, etc.) and the sys.dm_exec_describe_first_result_set function. These permissions are usually granted to most database users, but if you still hit issues, reach out to your DBA for assistance.

内容的提问来源于stack exchange，提问作者Henry