Drop all stored procedures in Firebird database

In my last post, I gave the SQL script to delete all stored procedures from a SQL Server database. Following is the link to post -

http://www.sanjaysingh.net/2010/12/drop-all-stored-procedures-from-sql.html

Now, following SQL script can be used to delete all the stored procedures from a Firebird database.

SET TERM ^ ;
EXECUTE BLOCK
AS
DECLARE procName varchar(100);
begin

FOR SELECT rdb$Procedure_name FROM rdb$procedures WHERE rdb$system_flag IS NULL OR rdb$system_flag = 0 INTO :procName
    DO
    begin
        EXECUTE STATEMENT 'DROP PROCEDURE ' || procName;
    end
end^

SET TERM ; ^

Drop all stored procedures from SQL Server database

Following SQL script drops all user stored procedures from a SQL Server database.

USE TestDatabase

DECLARE @CurrStoredProcedureName SYSNAME
DECLARE StoredProceduresCursor CURSOR FOR
    SELECT name FROM sys.objects WHERE type = 'P'

OPEN StoredProceduresCursor
FETCH NEXT FROM StoredProceduresCursor INTO @CurrStoredProcedureName
WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE('DROP PROCEDURE ' + @CurrStoredProcedureName)
    FETCH NEXT FROM StoredProceduresCursor INTO @CurrStoredProcedureName
END

CLOSE StoredProceduresCursor
DEALLOCATE StoredProceduresCursor

GO

Replace database name with your database name in the above script. Mainly we are doing following two things:

1. Get all the user stored procedures in a CURSOR.


2. Loop through the cursor, build a DROP PROCEDURE query for each stored procedure and execute it.

Check if given table or stored procedure exists in Firebird

Following SQL checks to see if table named Employee exists in the database and if it does, drops it.

SET TERM ^ ;
EXECUTE BLOCK AS BEGIN
if (exists(select 1 from rdb$relations where rdb$relation_name = 'EMPLOYEE')) then 
execute statement 'drop table employee;';
END^
SET TERM ; ^

Note that conditional operator IF cannot be used outside of PSQL so we have to place this call inside BLOCK as shown above. Also we cannot put DDL statements inside PSQL, so above thing cannot even be placed inside stored procedure.In the same way, following SQL checks to see if stored procedure Sel_Employee exists and drops it if it does.

SET TERM ^ ;
EXECUTE BLOCK AS BEGIN
if (exists(select 1 from RDB$PROCEDURES where rdb$Procedure_name = 'SEL_EMPLOYEE')) then 
execute statement 'drop procedure SEL_EMPLOYEE;';
END^
SET TERM ; ^

Password Manager

Often times we store our passwords in one or other kind of files on our personal computers. When you have too many passwords it becomes increasingly difficult to manage them. At the same time if you write them to a txt file, you also have the risk of it being read by someone else.

I made a very simple Password Manager C# .NET windows app that stores the password in encrypted format on your local file system. Its really simple. Following is summary of what it does.

• When you run the application first time, it will ask you to configure your master password.
• All the passwords are stored in encrypted format (using Rijndael symmetric algorithm) using your master password as the key in a file Password.dat at the exe location. Note that this file can only decrypted with your master password and so is secure even if someone gets his hand on your password data file.
• It has a a Form that shows your password in a grid and gives you a search text box which show real time search results as you type.

Source and executable can be downloaded from following locations:

http://sourceforge.net/projects/managepassword/files/

List all user tables and their columns names in a Firebird database

Following SQL query gives list of all the table names and their column names in a Firebird database.

SELECT Relations.RDB$RELATION_NAME "Table Name", Fields.RDB$FIELD_NAME "Column Name" FROM RDB$RELATION_FIELDS Fields
JOIN RDB$RELATIONS Relations ON
Fields.RDB$RELATION_NAME = Relations.RDB$RELATION_NAME
WHERE
(Relations.RDB$SYSTEM_FLAG IS NULL OR Relations.RDB$SYSTEM_FLAG = 0)
AND
(Relations.RDB$VIEW_BLR IS NULL)
ORDER BY 1;

As noted before, RDB$System_Flag is used to filter out user tables as against system tables and RDB$View_BLR is used to filter Views.

List all stored procedures in a Firebird database

Following SQL query gives you all the user define stored procedures in a Firebird database.

SELECT rdb$Procedure_name as "Procedure Name" FROM rdb$procedures
WHERE rdb$system_flag IS NULL OR rdb$system_flag = 0;

List all user table names in Firebird

Following SQL query will give you names of all the user defined tables in a Firebird database.

SELECT rdb$relation_name AS "Table Name"
FROM rdb$relations
WHERE rdb$view_blr IS NULL AND (rdb$system_flag IS NULL OR rdb$system_flag = 0);

rdb$system_flag of 0 or NULL identifies a user table or view and rdb$view_blr being NULL identifies that it is a TABLE.

Build Visual Studio solution from command line or batch file

Following simple batch file can be used to build a solution in Visual Studio 2010. Here I use devenv.exe to build the solution from command line so that we get the same behavior as if building from IDE. Save below given batch script into a .bat file, update the solution file path and you are all set to build.

echo off

call "C:\Program Files\Microsoft Visual Studio 10.0\VC\bin\vcvars32.bat"
devenv "C:\SampleWinApplication\SampleWinApplication.sln" /build

pause

vcvars32.bat sets up environment for Visual Studio Tools. Note that this same batch file is called when you invoke Visual Studio command prompt (Start->Programs->Microsoft Visual Studio 2010->Visual Studio Tools—>Visual Studio Command Prompt (2010). This is important to correctly invoke devenv from command line.