Sunday, June 28, 2015

Evaluating line of code in SQL Server

LOC in SQL Server


The Client ask you about the line of code in application source code. It's very easy for a developer to find out the line of code whenever you are using the IDE like Visual Studio.

Suppose Client as the line of code in Database. Then the developer think, how to evaluate out the line of code in SQL. There is no direct to  way to evaluate out. 

But a developer can evaluate the line of code for the logic written inside SQL Server regard a database. By using the sysobjects which are by the SQL server.

Where are line of code in SQL?


The developer/program write logic in SQL in
  • Stored Procedures
  • Views
  • Functions
  • Triggers   
These are the main objects where the line of code resides.


How to evaluate?

All these objects can be found using sys.all_sql_modules. sys.all_sql_modules contains the definition for all.
The developer use enter key to make easy under-stability of the sql instructions. Using the carriage return key , we can evaluate the line of code in SQL Server.


Demo with SQL Instructions.


using c, you can see definition of database object.

You can evaluate the line of code using the small script just pass all xtypes.

IsMSShipped is used to ignore the system object such as sys.views,sys.tables etc..

xtype is Object type. The following object types abbreviations are there in SQL Server:
  • AF = Aggregate function (CLR)
  • C = CHECK constraint
  • D = Default or DEFAULT constraint
  • F = FOREIGN KEY constraint
  • L = Log
  • FN = Scalar function
  • FS = Assembly (CLR) scalar-function
  • FT = Assembly (CLR) table-valued function
  • IF = In-lined table-function
  • IT = Internal table
  • P = Stored procedure
  • PC = Assembly (CLR) stored-procedure
  • PK = PRIMARY KEY constraint (type is K)
  • RF = Replication filter stored procedure
  • S = System table
  • SN = Synonym
  • SQ = Service queue
  • TA = Assembly (CLR) DML trigger
  • TF = Table function
  • TR = SQL DML Trigger
  • TT = Table type
  • U = User table
  • UQ = UNIQUE constraint (type is K)
  • V = View
  • X = Extended stored procedure


So Now It's very easy to evaluate the line of code for a database.

~
Sunit
technocrats@sunitkanyan.in