Getting SQL Script of database object of used Database using sys. sql_modules in SQL Server

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

Returns a row for each object that is an SQL language-defined module. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view

Refer the below Link to check the object Type of P, RF, V, TR, FN, IF, TF, and R.

http://www.c-sharpcorner.com/Blogs/9134/list-of-object-type-available-in-sql-server.aspx

The definition column contains the original SQL definition statements. SQL text that defines this module.

 If definition == NULL then it is Encrypted.

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

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

Query:

select * from sys.sql_modules

Let take an example of this object,

 

use northwind

select * from sys.sql_modules where object_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.