Getting SQL Script of database object using sys.syscomments in SQL Server

Getting SQL Script of database object using sys.syscomments in SQL Server

Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database.

The text column contains the original SQL definition statements

You can also search the text through all the script of objects.

Note: don't uses for development purpose, if you need it go for sys.sql_modules (Transact-SQL).

Query the below in SQL, and check the text column, then you can find the full SQL script.

Query:

select * from sys.syscomments

Let take an example of this object,

 

use northwind

select text from sys.syscomments where id=597577167

OUTPUT:

create view Invoices AS  SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,    Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City,    Customers.Region, Customers.PostalCode, Customers.Country,    (FirstName + ' ' + LastName) AS Salesperson,    Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName,    "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,    "Order Details".Discount,    (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight  FROM  Shippers INNER JOIN     (Products INNER JOIN      (      (Employees INNER JOIN        (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)       ON Employees.EmployeeID = Orders.EmployeeID)      INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)     ON Products.ProductID = "Order Details".ProductID)    ON Shippers.ShipperID = Orders.ShipVia 

 

Thanks for reading this article. Have a nice day.