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…
-
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 new windows…