SQL 2005 data dictionary (get code of stored procedure)

SQL 2005 data dictionary (get code of stored procedure)

• ALepage

  sp_helptext 'procName'

  this will give u the tsql of any procedure.

  the actual script and info about SPs is saved in syscomments system table/view. though u need to get the id of the SP first from sysobjects view.

  select * from syscomments where id= (select name from sysobjects where name = 'Myproc').

  
  
  

• Chuckw47

  Hello.
  I've had to create a routine in code to rebuild (run an alter command) on all programmable objects in a database for SQL 2000. I did that using the syscomments table. (I don't want to use sp_helpText or OBJECT_DEFINITION for that matter because i need to pull all the data at once from the server in order to avoid repeating calls). Now I need to make it word with SQL 2005. I saw that there is a backward-compatablity view in the sys schema so that my code dosen't really have to change, but in BOL says that I should consider using the new catalog views. And that is my question: Which table in SQL 2005 contains the information that was held in syscomments at SQL 2000
  
  
  thanks in advance,
  Amit
  

  
  
  

• Camey

  I would try SqlSpec - It's very cheap, fast, and generates very comprehensive documentation for any 2000 or 2005 database, including sprocs.  You can get it here: www.elsasoft.org
  

• CookieRevised

  Here is an example

  USE AdventureWorks
  GO

  SELECT SPECIFIC_NAME,ROUTINE_DEFINITION
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE SPECIFIC_NAME='uspGetBillOfMaterials'

  Denis the SQL Menace

  http://sqlservercode.blogspot.com/

  
  

• madhavan

  SQL Server 2005

  Consider the following Application that helps to create the Training Calendar for a month. Calendar Creation happens in 2 steps.

  Step 1: Create the calendar code , description and the period

  Step 2: Specify the programs, modules and courses in the calendar.

  Step 1: Create the calendar code , description and the period

  The first Screen is used to create the calendar Code and also the period for which the calendar is to be created. The Logic to be executed on each event (button click, Screen Load etc) is to be implemented by writing an SP.

  Write SPs for the following

  1. The Combo “Training Calendar Unit” has to be loaded with the Value “Ramco Unit” as soon the screen is launched. Write an SP that will output this value

  2. The Combo “Status” has to be loaded with the value “Open” and “closed”. Write an SP that outputs these values

  3. When the Save button is clicked , the calendar code along with the period for which it is created has to be saved in the table “Calendar” with has the fields (Calendar_Code, Description, Training_Unit, Period_From, Period_To) .
  Write an SP that takes these parameters from the screen , Validates the inputs and inserts the same into the calendar table. Perform the following Validations

  a. Calendar Code should not already exists in the table

  b. Calendar description cannot be empty

  c. Calendar from period has to be earlier than the Calendar To period. And the calendar From period has to be greater than the system date

  
  

  
  

  Step 2: Specify the programs, modules and courses in the calendar.

  After the User saves the calendar details and clicks on the hyperlink, “Edit Programs / Modules in the calendar” link the following screen (see Figure 1.1) is launched, where the user enter the details about the Programs, Modules and courses in the calendar.

  Note : Apart from inserting the details about the Calendar code,description, From Date and TO Date of the calendar in the calendar master table, create another temporary table (this table should be dropped after the calendar is created) where the name of the table would contain the calendar code, entered by the user. The name of the table would start with the keyword TAB and followed by the calendar code. Insert the calendar code, desc , from date and to date in that table.

  As soon as the following Screen is launched, the details saved in the temporary table should be used to populate the screen controls. Write a Stored procedure for the same

  The user is required to enter the details about the

  a. Programs

  b. Modules

  c. Courses
  that form a part of the training calendar.

  A training calendar can have one or more Programs , Modules and Courses

  There should be atleast one Program or one module or one course scheduled for every calendar.

  Example of a course is “Introduction to Oracle 9i SQL” or “Oracle 9i PL/SQL” Which in turn forms a part of the Module “Oracle 9i Programming”. Hence a Module can contain one or more courses. A module can be a part of a Program .

  The Module “Oracle 9i Programming” can be a part of “Developers Training Level 1” program . This training program can contain the “Oracle 9i Programming Module” and many other modules.

  The training can also be schedule for a single course, such as “Introduction Oracle 9i SQL”.

  
  

  FIGURE 1.1

  Save Draft

  Save Final

  
  

  The user enters the details about the Programs ,Modules and courses.

  When the user clicks on the “Save Draft” button the details about the calendar has to be saved in the following tables, Calendar_Programs, Calendar_Modules and Calendar_Courses. The button “Save Draft” allows the user to provide incomplete details also. The user can once again edit the calendar and furnish complete infomaiton.

  Write an SP to perform the Save operation

  The SP should ensure that the start date of the program is earlier than the end date and both the start date and end date has to fall within the from and to dates of the calendar.

  The program names , module names and course names must exist in the program_master, module_master and course_master tables repectively

  The calendar can be published to all users only when the Final version it is saved. When the User clicks “Save Draft” button only a draft version of the calendar is created and hence it is not available for furthur processing. Maintain a flag in the Calendar table that indicates whether a final version of the calendar has been saved or not.

  The user is once again required to populate the details of this calendar and click on the Save Final button

  When the user clicks on the Save Final button the details of the calendar should be saved in the Calendar_Programs, Calendar_Modules and Calendar_Courses tables. Check if the user has already recorded any partial information about the calendar by clicking on the “Save Draft” button, in which case the existing records should be updated. Else insert the records into these tables. After insertion the flag in the calendar master table should indicate that the calendar is complete and can be published for the reference of others

  Perform the following validations in the SP

  1. Ensure that atleast one program or module or course is a mapped in the calendar

  2. The start date has to be earlier than the end date

  3. The start date and end date to lie within the From date and To Date of the calendar

  CAN U PLEASE GIVE ME THE CODING FOR STORED PROCEDURE ALONE

  PLEASE

  
  
  

• Marsha

  Jezemine,

  You're right about SQLSpec - it's the best, and cheapest!, SQL Server documentation tool that I've seen. Best $50 I've spent on database software. Thanks for mentioning it as I wouldn't have found it otherwise.

  Cheers, David.

  
  

• nadiasaku

  sys.sql_modules is the new catalog view

  SELECT obj.name , mod.definition,obj.object_id,len(mod.definition) FROM sys.sql_modules mod INNER JOIN sys.objects obj ON mod.object_id = obj.object_id WHERE obj.type = 'p'
  
  SELECT o.name, m.Text ,o.Object_ID, m.id,len(m.Text) FROM syscomments m INNER JOIN sys.objects o ON m.id = o.object_id WHERE o.type = 'p'

  Madhu

  
  
  

• smar

  The routine_definition column in this view is restricted to 4000 characters only. So it will not suffice for modules whose definition exceed the length. The correct way to get the definition is to use OBJECT_DEFINITION built-in or sp_helptext or the SMO scripting classes.
  
  

• Ferentix

  Hello.
  I've had to create a routine in code to rebuild (run an alter command) on all programmable objects in a database for SQL 2000. I did that using the syscomments table. (I don't want to use sp_helpText or OBJECT_DEFINITION for that matter because i need to pull all the data at once from the server in order to avoid repeating calls). Now I need to make it word with SQL 2005. I saw that there is a backward-compatablity view in the sys schema so that my code dosen't really have to change, but in BOL says that I should consider using the new catalog views. And that is my question: Which table in SQL 2005 contains the information that was held in syscomments at SQL 2000
  
  
  thanks in advance,
  Amit
  

  
  
  
• SQL 2005 data dictionary (get code of stored procedure)