Category Database

Remove duplicate rows from a table in SQL Server

Hello Friends, I have tried to remove the duplicate rows from the table using row_number function. Here I am explaining the demo using the temp table. Steps: 1. Create Temp Table or Select your table Create Table #Main ( id int, item varchar(100) ) 2. Insert some records insert into #main values (119,1) insert into […]

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, […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]