Saturday, September 10, 2011

DefnCopy Utility! How to get Views, Stored Procedures, Trigger structure in a text file?

Views, Stored procedures and triggers - sometimes we require to extract the structure or definition of these database objects in a text file, so what to do if somone asks us to do the same?
Don't worry! Defncopy to the rescue!!

Now what is Defncopy? Never heard about it. No worries! Today we will explain you and give you a glimpse about this excellent utility.

Defncopy is a command line utility program provided by Sybase Inc (now SAP company) with the Sybase Adaptive Server Enterprise RDBMS to achieve this for us. For Example - If we have a stored procedure – MyProcedure1 which has the following definition –

create proc MyProcedure1
  @MyVar varchar(100)
as
begin
   select * from MyTable whereName = @MyVar
end
go

and if we want to get this definition of compiled stored procedure MyProcedure1 in a text file then we can use the Sybase Inc provided DefnCopy utility. This utility is also available when we have only the client program installed on our machine.

The syntax for DefnCopy utility via the command prompt is as follows –

defncopy -S <<SERVER NAME>> -U<<USER NAME>> -P <<PASSWORD>> out <<FILE WITHCOMPLETE PATH>> <<DATABASE>> <<OBJECT NAME>>

In the above syntax <<OBJECT NAME>> can be either a stored procedure, trigger, view etc whose definition exists in the syscomments table.

The only catch is that we cannot extract tables definition via this utility as for a table definition we need to query sysobject and syscolumns tables and this definition does not exist in the syscomments table.

Please keep in mind that there is a space between each of the options (switches)specified above and the option value which means –

There is a space between –S and<<SERVER NAME>> in above syntax.

Please note - This utility can be run through the command prompt only or via using the extended stored procedure for cmd.

No comments:

Post a Comment