Script database with Azure Data Studio

Question

https://learn.microsoft.com/en-us/sql/ssms/tutorials/media/scripting-ssms/scriptdb.png?view=sql-server-ver15

I would like to complete the step above with Azure Data Studio and I am not sure how to. I have previous experience with SQL Server but new to ADS interface. Help would be much appreciated! :)

Be well!

Strother

Hi amau, If my answer is helpful for you, you can mark it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Dependent

Answer 1

You can install the official Microsoft extension Database Administration Tool Extensions for Windows:

It adds the Generate Scripts wizard in the context menu of a selected database as in SSMS:

Spalla

Anyone know if it works for Azure Data Studio for Mac? (I'm not a Mac fan, but my company "highly encourages" Mac on the developers for some reason) – Dextrous

@Dextrous Unfortunately I don't know it! – Spalla

Wonderful, I serached for an extension up and down using keywords like "Export" but there were no results. – Pomfrey

Database Administration Tool Extensions for Windows is available on Mac since at least Januari 2023 – Yu

Answer 2

Please reference this tutorial: Generate data scripts using SSMS and Azure Data Studio.

We can not find any Extensions support script database as create in Azure Data Studio, it only supports script table as create with the Extension Simple Data Scripter:

For example:

Hope this helps.

Dependent

Answer 3

Approach 1:

If python can be installed in the system, then we can use mssql-scripter to connect to the database and generate both schema and data scripts.

Installation: https://github.com/microsoft/mssql-scripter

Usage: https://github.com/microsoft/mssql-scripter/blob/dev/doc/usage_guide.md

Approach 2:

Since I couldn't get any new installations done in the remote system which I am working on and With the current extensions not supporting database scripting, I have to fall back to basic 'sys' schema to achieve this. Came up with a simple query to script the 'User Defined Table' definitions alone:

CREATE TABLE #CreateQueries
(
    QueryString NVARCHAR(MAX)
)

DECLARE @Counter INT 

DECLARE  
      @object_name SYSNAME  
    , @object_id INT  
    , @SQL NVARCHAR(MAX)  

SET @Counter=0
WHILE ( @Counter <= ( SELECT COUNT(*) FROM sys.objects WHERE type = 'U' ) )
BEGIN

    SELECT  
      @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'  
    , @object_id = [object_id]  
FROM (SELECT [object_id] = OBJECT_ID('dbo.' + name, 'U') FROM sys.objects WHERE type = 'U' ORDER BY name OFFSET @Counter ROWS FETCH FIRST 1 ROWS ONLY) o

SET @SQL = ''
      
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) +   
        
        TRIM(' ,' FROM (SELECT ' , [' + c.name + '] ' +   
        CASE WHEN c.is_computed = 1  
            THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)  
            ELSE   
                CASE WHEN c.system_type_id != c.user_type_id   
                    THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'   
                    ELSE '[' + UPPER(tp.name) + ']'   
                END  +   
                CASE   
                    WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')  
                        THEN '(' + CASE WHEN c.max_length = -1   
                                        THEN 'MAX'   
                                        ELSE CAST(c.max_length AS VARCHAR(5))   
                                    END + ')'  
                    WHEN tp.name IN ('nvarchar', 'nchar')  
                        THEN '(' + CASE WHEN c.max_length = -1   
                                        THEN 'MAX'   
                                        ELSE CAST(c.max_length / 2 AS VARCHAR(5))   
                                    END + ')'  
                    WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')   
                        THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'  
                    WHEN tp.name = 'decimal'  
                        THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'  
                    ELSE ''  
                END +  
                CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id   
                    THEN ' COLLATE ' + c.collation_name  
                    ELSE ''  
                END +  
                CASE WHEN c.is_nullable = 1   
                    THEN ' NULL'  
                    ELSE ' NOT NULL'  
                END +  
                CASE WHEN c.default_object_id != 0   
                    THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +   
                         ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)  
                    ELSE ''  
                END +   
                CASE WHEN cc.[object_id] IS NOT NULL   
                    THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]  
                    ELSE ''  
                END +  
                CASE WHEN c.is_identity = 1   
                    THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +   
                                    CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'   
                    ELSE ''   
                END   
        END  
    FROM sys.columns c WITH(NOLOCK)  
    JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id  
    LEFT JOIN sys.check_constraints cc WITH(NOLOCK)   
         ON c.[object_id] = cc.parent_object_id   
        AND cc.parent_column_id = c.column_id  
    WHERE c.[object_id] = @object_id  
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE)
    .value('.', 'NVARCHAR(MAX)') ) + 

    ISNULL( (   SELECT ', CONSTRAINT [' + i.name + '] PRIMARY KEY ' + i.type_desc + ' ' + 
                '[' + COL_NAME(ic.[object_id], ic.column_id) + ']' +  
                CASE WHEN ic.is_descending_key = 1  
                    THEN ' DESC'  
                    ELSE ''  
                END  
        FROM sys.index_columns ic WITH(NOLOCK)  
        JOIN sys.indexes i
        ON i.[object_id] = ic.[object_id]  
            AND i.index_id = ic.index_id 
            AND i.[object_id] = @object_id 
            AND i.is_primary_key = 1
            FOR XML PATH(''), TYPE)
    .value('.', 'NVARCHAR(MAX)'), '') + ');' + CHAR(13) +

    ISNULL( ( SELECT 'ALTER TABLE '+ @object_name + 
        ' ADD CONSTRAINT [' + f.name +'] FOREIGN KEY (['+
        COL_NAME(f_k_c.[parent_object_id], f_k_c.[parent_column_id])+ ']) REFERENCES '+ 
        '[' + OBJECT_SCHEMA_NAME(f_k_c.referenced_object_id) + '].[' + OBJECT_NAME(f_k_c.referenced_object_id) + ']' +
        ' (['+COL_NAME(f_k_c.[referenced_object_id], f_k_c.[referenced_column_id])+'])'+CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)
        FROM sys.foreign_keys f WITH(NOLOCK)
        JOIN sys.foreign_key_columns f_k_c WITH(NOLOCK) ON
        f_k_c.constraint_object_id = f.object_id
        AND f.parent_object_id = @object_id
        FOR XML PATH(''), TYPE)
    .value('.', 'NVARCHAR(MAX)'), '')


    SET @Counter = @Counter + 1

    INSERT INTO #CreateQueries VALUES (@SQL)

  
END

SELECT * FROM #CreateQueries ORDER BY QueryString;

Hardcoded the 'dbo' schema for now. Can be extended further with other index scenarios as well to support as a good work around for the current scenario.

Loverly