Generate Scripts equivalent in TSQL

Question

I am not sure if there's an equivalent TSQL script for this.

I want to Generate Scripts using TSQL code similar to how backup and others items work in SSMS

for example in backup

clicking the script action it will generate this tsql script

BACKUP DATABASE [prosmartexamples.prosmarttools] TO DISK = N'D:\Backup\example.bak' WITH NOFORMAT, NOINIT, NAME = N'example.test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

Now, I want it also to work the same in Generate Scripts but I dont know how to get the equivalent of it in TSQL

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

Script ... scripts current action that was perfromed in GUI, here taking backup.

Generate Scripts from object explorer was designed to script database objects(tables, views, functions,...).

Get script for every action in SQL Server Management Studio

SQL Server Management Studio (SSMS) provides a very good option to generate scripts for any operation performed through the GUI. It is an effective way to save the T-SQL code of actions performed through SSMS. Here is list of some of the tasks categories for which you may generate T-SQL scripts from SSMS GUI actions

Changing any server instance level option

Changing any database level option

Managing server roles, logins, permissions

Managing database roles, users, permissions

Backup/Restore operations

Managing policies (SQL Server 2008)

Generate Scripts (SQL Server Management Studio)

You can create scripts for multiple objects by using the Generate and Publish Scripts Wizard. You can also generate a script for individual objects or multiple objects by using the Script as menu in Object Explorer.

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 15 '19 at 09:13

Lukasz Szozda

162,964
23
234
275

i dont get what do you want to say – Rashid Aug 15 '19 at 09:15
@SecretCoder Long story short action(backup) != object(table) – Lukasz Szozda Aug 15 '19 at 09:16
so what you mean is it is not possible to in TSQL to generate a script. because I want to transfer data for whole tables in my existing database to a new database with the same structure using code either TSQL or C# – Rashid Aug 15 '19 at 09:19
@SecretCoder It is a XY Problem then. Please extend your question. You have multiple options: backupt/restore, generating dacpac/bacpac file, SSIS, script table objects ... – Lukasz Szozda Aug 15 '19 at 09:20

score 0 · Answer 2 · answered Aug 15 '19 at 11:59

I believe your looking for this option:

it generate the schema of database along with the data in T-SQL format.

or if your looking just the script of Generate Script task i think, that's not possible. Alternatively, you can try following (unfortunately User tables not extractable in this method), for tables:

SELECT  DB_NAME() as DBName, o.object_id, OBJECT_NAME (o.object_id) as ObjectName,
        type_desc,
        OBJECT_DEFINITION (o.object_id) as ObjectDefination
FROM sys.objects as o
where o.type in ('V', 'P', 'TR')
go

Generate Scripts equivalent in TSQL

2 Answers2