To get definition of different type of object using sqlcmd utility from Microsoft SQL Server 2019

Copper Contributor

Hi,

I am using ODBC based sqlcmd utility from command line with switches and passing TSQL query using sys.sql_modules catalog view to get the definition of sql object of type by passing the object-name: Stored procedures (P), Functions(FN, IF, TF), Triggers(TR), Views(V) from Microsoft SQL Server 2019 database and store it locally in .sql file using below query successfully:

sqlcmd -S "SERVER" -U "USERNAME" -P "PASSWORD" -d DATABASE -Q "SET NOCOUNT ON SELECT [definition] FROM sys.sql_modules WHERE object_id = (OBJECT_ID(N'OBJECT-NAME')) SET NOCOUNT OFF;" -y 0 -u -o "PATH-TO-STORE-FILE.sql"

 

Using ODBC based sqlcmd utility from command line, I need help for query to get definition of sql object of type: Table, Sequence, Queue, Default constraints, Check constraints, Primary key, unique key, Index. (I need exact definition I get from SSMS Generate script wizard gui to create a Transact-SQL script) where in sqlcmd by passing server, username, password, database_name, query with for example table object-name: 'tb_esu0' (to get its definition) and local path to store .sql file.

Appreciate your help!

3 Replies

to get definition of sql object of type: Table, Sequence, Queue, Default constraints, Check constraints, Primary key, unique key, Index.


@Khushboo55 , surely you can query all object defenitions, but because all objets are different in structure you have to write different per each.

One way ist to use System Information Schema Views (Transact-SQL) - SQL Server | Microsoft Learn

@olafhelper Thank you! I have tried Information schema view but that didn't work.

 

I am using different query for each object type.

For sql object of type : Stored Procedure, Function, Trigger and View, I am getting definition using sys.sql_modules catalog view.

For sql object of type: Default constraint, I am getting definition using sys.default_constraints catalog view.

For sql object of type: Check constraint,  I am getting definition using using sys.check_constraints catalog view.

 

I need help to get definition of this objects: 

Table, Sequence, Queue, Primary Key, Unique Key, Index. using command line query (I need exact definition I get from SSMS Generate script wizard gui to create a Transact-SQL script)

 

Appreciate your help!

 

See the link I posted, you can query all informations from system views.