Get Parameter List from StoreProcedure or Function in SqlServer 2005

–get all storeprocedure and function

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS

–pass storeprocedure name or function name

–get the relavent columns ParameterName,DataType,Length,ParameterType

SELECT Parameter_name as ParameterName,Data_type as DataType,coalesce(Character_Maximum_Length,0) as Length,Parameter_Mode as ParameterType FROM INFORMATION_SCHEMA.PARAMETERS

WHERE SPECIFIC_NAME=’sp_InsertOrder’

Get Primary key – Foreign key relations table in sql server 2005

Why it is required?

The main purpose of this is finding related constraint table from database.

Check below Query.

–get table list with constraint(primary and foreign key)

select * from information_schema.constraint_column_usage

–get table list with foreign key constraint

select * from information_schema.referential_constraints

–get the relation

select

tblAll.table_name as PrimaryTableName,

tblAll.column_name as PrimaryTableColumn,

tblFK.table_name as ForeignKeyTable,

tblFK.column_name as ForeignKeyColumn

from information_schema.constraint_column_usage tblAll

inner join information_schema.referential_constraints tblAllFK on tblAllFK.unique_constraint_name = tblAll.constraint_name

inner join information_schema.constraint_column_usage tblFK on tblAllFK.constraint_name=tblFK.constraint_name

Thnx

The media set has 2 media families but only 1 are provided

I got this error when restore database from .bak file.

why this error happen?

when you backup the database at that time you have not delete the existing path means you start a back up behalf of the previous one means your backup is divided in to more parts.

steps :
1)  you select all the related .bak file.

example here mydatabase name is test1. first i backup that at that time mybackup name is test1_17.bak. second backup i take at 18 and backup file name is test1_18.bak. now when you selecte restore database at that time you selected both this file.

rightclick on database –> task –> Restore –> Database

open one dialog box — now select fromdevice and add related files (test1_17.bak and test1_18.bak).

* when you take a backup at that time you remove the previous file.

Thnx.

Regular Expression in Sqlserver 2005

why Regular Expression Required for sqlserver 2005?

The basic need Of Regular Expression is avoiding hard work for searching or pattern maching in database. Example if i want to search valid email address from users table then what will be senario for that? i have thousand of newly registered user daily. then how to i find invalid Email Address from that?

let’s start with regular expression in sqlserver 2005.

the basic requirement for that configure OLE Object in to Server.

–Configure OLE Automation object.
–EXECUTE permission set for object ’sp_OACreate’
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OASetProperty] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OAMethod] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OAGetErrorInfo] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OADestroy] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OAStop] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OACreate] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OAGetProperty] TO[public]
GO
sp_configure’show advanced options’,1
GO
reconfigure
go

execsp_configure
go
execsp_configure‘Ole Automation Procedures’,1
– Configuration option ‘Ole Automation Procedures’ changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go

steps :

1) Create Scalar Value Function In Sqlserver 2005.

create FUNCTION dbo.RegEx
    (
        --Expression Target / Source
        @Target varchar(5000),
        --Regular Expression Pattern
        @Pattern varchar(Max),
        --whether the expression case sensitive
        @CaseSensitive bit = 0
    )
RETURNS bit
AS
    BEGIN
        DECLARE @ReturnOACreate int
        DECLARE @ObjToken int
        DECLARE @objMatches int
        DECLARE @objMatch int
        DECLARE @count int
        DECLARE @results bit

        EXEC @ReturnOACreate = sp_OACreate 'VBScript.RegExp', @ObjToken OUTPUT

        --objecttoken OUTPUT
        --Is the returned object token, and must be a local variable of data type int.
        --This object token identifies the created OLE object and is used in calls to the other
        --OLE Automation stored procedures.
        --Return
        --0 (success) or a nonzero number (failure) that is the integer value of the
        --HRESULT returned by the OLE Automation object

        IF @ReturnOACreate <> 0 BEGIN
            SET @results = 0
            RETURN @results
        END

        EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'Pattern', @Pattern

        IF @ReturnOACreate <> 0 BEGIN
            SET @results = 0
            RETURN @results
        END

        EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'Global', false
        IF @ReturnOACreate <> 0 BEGIN
            SET @results = 0
            RETURN @results
        END

        EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'IgnoreCase', @CaseSensitive
        IF @ReturnOACreate <> 0 BEGIN
            SET @results = 0
            RETURN @results
        END    

        EXEC @ReturnOACreate = sp_OAMethod @ObjToken, 'Test', @results OUTPUT, @Target
        IF @ReturnOACreate <> 0 BEGIN
            SET @results = 0
            RETURN @results
        END

        EXEC @ReturnOACreate = sp_OADestroy @ObjToken
        IF @ReturnOACreate <> 0 BEGIN
            SET @results = 0
            RETURN @results
        END
    --return 1 for success
    RETURN @results
    END

2) Now use this function for Matching the Expression.

case 1: simple regular expression match

select dbo.RegEx( N'122-45-6789', N'^\d{3}-\d{2}-\d{4}$',0 )

run this it return 0 it indicate this expression is valid.

select dbo.RegEx( N'1224-45-6789', N'^\d{3}-\d{2}-\d{4}$',0 )

run this it return 1 it indicate this expression is InValid.

case 2: Get the data from Table

now the senario is i am finding the valid email address from the table (here i used simple regular expression 

which is made manual for demo purpose you can use any of them.)

declare @tblTmp as table(id int identity(1,1),emailaddress varchar(50))

insert into @tblTmp values
('test@yahoo')
insert into  @tblTmp values
('test@gmail.test')
insert into @tblTmp values
('t@gmail.com')
insert into @tblTmp values
('test@test.com')
insert into  @tblTmp values
('test@test.')

select * from @tblTmp

--find valid email address
select * from @tblTmp where
dbo.RegEx(emailaddress,'^[A-Za-z0-9._%-]+@[a-zA-Z0-9]+\.[a-zA-Z]{2,4}$',0)=1

Thnx.

 Reference :

1) MSDN

FullText Search In SqlServer 2005

why fulltext search used?

• Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a SQL Server Database. Unlike the LIKE predicate, which only works on character patterns, full-text queries perform a linguistic search against this data, operating on words and phrases based on rules of a particular language.
• The performance benefit of using full-text search can be best realized when querying against a large amount of unstructured text data. A LIKE query (for example, ‘%microsoft%’) against millions of rows of text data can take minutes to return; whereas a full-text query (for ‘microsoft’) can take only seconds or less against the same data, depending on the number of rows that are returned.

Keyword Used In FullText Search

   1)FreeText 2)FreeTextTable 3)Contains 4) ContainsTable

 

1)FreeText

• Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition.

2)FreeTextTable

• Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can be referenced in the FROM clause of a SELECT statement like a regular table name.

3)Contains

• A word or phrase.
• The prefix of a word or phrase.
• A word near another word.
• A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
• A word that is a synonym of another word using thesaurus (for example, the word metal can have synonyms such as aluminum and steel).

4)ContainsTable

• Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.
• Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.

 

I am use the sql server version Standard Edition.

 

Steps:

 

1) Enabled the fulltext on the database using the sp.

   EXEC sp_fulltext_database 'enable'

2) Create Table in Database 

 

CREATE TABLE [dbo].[UserDetail](

[UserDetailId] [int] IDENTITY(1,1) NOT NULL,

[UserId] [int] NULL,

[Address1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Address2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 CONSTRAINT [PK_UserDetail] PRIMARY KEY CLUSTERED

(

[UserDetailId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

3) Insert Record Into Table

 

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(1,’nava india’,'programming’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(3,’nava america’,'Developing’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(5,’juna india’,'Testing ‘)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(6,’juna america’,'QA’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(8,’india’,'PM’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(3,’japan’,'Developing’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(3,’nava japan’,'Developing’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(1,’juna japan’,'programming’)

INSERT INTO [UserDetail]

([UserId],[Address1],[Address2])

VALUES

(1,’america nava’,'programming’)

4) Enabled Full Text in Table

    Right Click On Table

     * Select Full Text Index

       * Define Full Text Index

 

Click Next –> Next

 

 

–here you select Field where you apply full text search field.

select both the field and press Next.

In Next Select automatic and Press Next.

In Next Select Full Text Catalog and Press Next.

Press Next –> Next –> Press Finish Button

 

5) Query Using FullText Search Keyword

5.1) FreeText

• select * from userdetail where FreeText(Address1,‘nava juna america’)

          Search From Address1 Field where keyword like nava,juna,america.

• select*fromuserdetail where FreeText(*,‘nava india QA’)

Search From Address1,Address2 Field where keyword like nava,india,QA

5.2 ) FreeTextTable

• SELECT  * FROM UserDetail AS FT_TBL  INNER JOIN FREETEXTTABLE(UserDetail,Address1,‘nava juna’) AS KEY_TBL

         ON FT_TBL.UserDetailId = KEY_TBL.[KEY];

         –TableName -UserDetail

         –Column Name – Address1

         –Search Keyword -words

Search From Address1 field where keyword like nava juna

• SELECT * FROM UserDetail AS FT_TBL INNER JOIN FREETEXTTABLE(UserDetail,*, 

  ‘nava PM’) AS KEY_TBL ON FT_TBL.UserDetailId = KEY_TBL.[KEY];

     Search From Address1,Address2 field where keyword like nava,PM

 

5.3)CONTAINS

• select * from userdetail where Contains(address1,'nava')

search from Address1 where keyword like nava

select * from userdetail where Contains(address1,'nava juna')

you can get the error if you write this with contain keyword.

• select * from userdetail where Contains(address1,‘”nava” or “india”‘)

          Search From Address1 where keyword like nava or india

 

• select * from userdetail where Contains(address1,‘”na*”‘)
• select*fromuserdetail where Contains(*,‘”nava” or “india” or “PM”‘)

         Search From Address1,Address2 where keyword like nava or india or PM

• select * from userdetail where Contains(*,‘ISABOUT (nava weight (0.8))’)

 

 

Thanks.

 

Reference Site :

MSDN Help

http://msdn.microsoft.com/en-us/library/ms345119.aspx

use case in where clause in sqlserver 2005

steps:

–declare temp table
declare@tbl as table(id int,username varchar(50),Age int)
–insert data into temp table
insert into @tbl
values
(1,‘Amit’,10) 

insert into @tbl
 

insert into @tbl

values
(1,‘Bhavin’,10) 

insert into @tbl
 

insert into @tbl

values
(1,‘Chirag’,10) 

insert into @tbl
 

insert into @tbl

values
(1,‘Dhaval’,10) 

insert into @tbl
 

insert into @tbl

values
(1,‘Nirav’,10) 

insert into @tbl
 

insert into @tbl

values
(1,‘kumar’,10)

–declare variable
declare @username varchar(50)
set @username =‘a’ 

select * from @tbl  

select * from @tbl

where
case @username
     whennullthen1
     when‘a’then
          case whenusername like‘a%’then1 else0 end
     when‘b’then
            case whenusername like‘b%’then1 else0 end
end =1

Recursion Function In SqlServer 2005

Create the function which get the total no of child in the parent node.

Steps:

1)      create the table called (tempRecursion)

Fields :

Id int

PId int 

Summary varchar(50) 

Id Pid summary

1   0    a

2   1    b

3   1    c          

4   2    s

     2) now create the function which argument is (id,child count)

FUNCTION [dbo].[fntempRecursion]

(@id int, @getChildInfo int)

bigint

AS

 

BEGIN

 

 

 

 

SET @getChildInfo = @getChildInfo + ( 

 

SELECT Count(id) FROM  tempRecursion WHERE Id =@id )

declare @ref int set @ref =-1 

set @ref =( select pid from tempRecursion where id =@id)

–indicate first node

if(@id <>0 )

( 

SELECT @getChildInfo = dbo.CountChildren(ID, @getChildInfo) FROM

WHERE pid = @id )

END

 

RETURN  @getChildInfo

 

3) run the following query

dbo.fntempRecursion(1,0)

 

Thnx

Get The Database And Table From SqlServer Using SMO (Sql Management Object

This is new technology called SMO(sql management object). You can give the server name and get the total no of database. After the selecting particular database you can got the total no of table and store procedure.

 

Here is the example of the. I used this in the windows application.

 

Steps

1) Create new windows Application (whatever you preferred (vb.net or c#.net))

2) Now add the references on that which are below

2.1) Microsoft.Sqlserver.ConnectionInfo

2.2.) Microsoft.Sqlserver.Smo

2.3) Microsoft.Sqlserver.SmoEnum

3) add the namespace into the project (specifically on form where you want to add)

using Microsoft.SqlServer.Management;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

4) no of control on the form which are below

4.1 ) button (name : btnGetServer)

4.2) TextBox (name : txtServerName)

4.3) Combobox (name : cmbDataBase)

4.4) Combobox (name : cmbTableName)

4.5) Label (name : lblTotalNoOfTable)

4.6) Combobox (name : cmbSp)

4.7) Label (name : lblTotalNoOfSP)

5) Now On the form at the global level (means at the form level create one Server Variable)

5.1) Server objserver;

6) click on the btnGetServer (it generated the click event of the button) put the below code in to that.

// To Connect to SQL Server

// use the Connection from the System.Data.SqlClient Namespace.

//you can specify the connection string for that

SqlConnection sqlCon = new SqlConnection(@”Data Source=”+ txtServerName.Text+“;uid=(EnterUserId);password=(Password)”);

//build a “serverConnection” with the information of the sqlConnection” ServerConnection serverCon = new ServerConnection(sqlCon);

//The “serverConnection is used in the ctor of the Server.

objserver = new Server(serverCon);

cmbDataBase.Items.Clear();

for (int i = 0; i < objserver.Databases.Count; i++)

{ cmbDataBase.Items.Add(objserver.Databases[i].Name.ToString());

}

 

 

 

 

 

7) on selected index change event of the cmbdatabase put the below code.

//clear the item from cmbsp (combobox)

cmbSP.Items.Clear();

//clear the item from cmbTableName (combobox)

cmbTableName.Items.Clear();

//select the total no of table from the selected databae

for (int i = 0; i < objserver.Databases[cmbDataBase.SelectedItem.ToString()].Tables.Count; i++)

{ cmbTableName.Items.Add(objserver.Databases[cmbDataBase.SelectedItem.ToString()].Tables[i].Name.ToString());

}

//select the total no of storeprocedure from the selected database //(including the system store procedure)

for (int i = 0; i < objserver.Databases[cmbDataBase.SelectedItem.ToString()].StoredProcedures.Count; i++)

{ cmbSP.Items.Add(objserver.Databases[cmbDataBase.SelectedItem.ToString()].StoredProcedures[i].Name);

}

//display information in lable

lblTotalNoOfSP.Text = “NoOfSp In Database Are :” + cmbSP.Items.Count;

lblTotalNoOfTable.Text = “NoOf Table In Database Are :” + cmbTableName.Items.Count;

check in to the application.

Thanks.