Check if Server, DB and SP exists

Hai
i am using VB.NET 2003 windows Application... In that i want to connect to SQL Server DataBase dependng on the Server Name AND DataBase name thats i enter in the textbox ... But i want to check if this server and database exists ... Also i want to execute a stored procedure.. That stored procedure Name is also entered in the text box .. before executing the stored procedure i want to check if that stored procedure exists ...How can i check these using VB.net code ? How shud i do the coding ?
If anybody knows plz do help me .. Any Help Appreciated ..

Hi,
U can do this by creating 2 SP.
First SP is for the database existence:
select * from sys.databases where name = 'DatabaseName'

Second SP is for the Procedure existence:

CREATE PROCEDURE [dbo].[ExistenceOF_sp]
    @DB SysName,
    @Name SysName,
    @Type nvarchar(1000)
AS
    BEGIN
        SET NOCOUNT ON ;

DECLARE @Cmd NVARCHAR(MAX)
Declare @Out Table ( ResultSet XML )

IF LEFT(@Type, 1) != '('
SET @Type = '(' + CHAR(39) + @Type + CHAR(39) + ')'

Set @CMD = 'Select * from [' + @DB + '].sys.objects where name=' + CHAR(39) + @Name + CHAR(39) + ' and type in ' + @Type + ' For XML Auto,Type'

Insert into @Out
EXEC ( @CMD )

        IF EXISTS ( Select *
                    from    @Out
                    where   ResultSet is not null )
            RETURN 1
        ELSE
            RETURN 0
    END   -- Procedure: ExistenceOF_sp

This is the example to call this SP:

Declare @RetVal INT

exec @RetVal=ExistenceOF_sp @DB='master', @Name='MSreplication_options', @Type=N'( ''U'', ''V'')'

Select @RetVal

Happy Coding!!

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Blocked Account18y ago
Hi,
    U can do this by creating 2 SP.
First SP is for the database existence:
select * from sys.databases where name = 'DatabaseName'

Second SP is for the Procedure existence:

CREATE PROCEDURE [dbo].[ExistenceOF_sp]
    @DB SysName,
    @Name SysName,
    @Type nvarchar(1000)
AS
    BEGIN
        SET NOCOUNT ON ;

        DECLARE @Cmd NVARCHAR(MAX)
        Declare @Out Table ( ResultSet XML )

        IF LEFT(@Type, 1) != '('
            SET @Type = '(' + CHAR(39) + @Type + CHAR(39) + ')'

        Set @CMD = 'Select * from [' + @DB + '].sys.objects where name=' + CHAR(39) + @Name + CHAR(39) + ' and type in ' + @Type + ' For XML Auto,Type'

        Insert into @Out
                EXEC ( @CMD )

        IF EXISTS ( Select *
                    from    @Out
                    where   ResultSet is not null )
            RETURN 1
        ELSE
            RETURN 0
    END   -- Procedure: ExistenceOF_sp

This is the example to call this SP:

Declare @RetVal INT

exec @RetVal=ExistenceOF_sp @DB='master', @Name='MSreplication_options', @Type=N'( ''U'', ''V'')'

Select @RetVal

Happy Coding!!
0