How can I open an .MDF file in SQL Server? sql server 2008

Attaching .MDF file in SQL Server  Let’s say someone wants to give you their SQL Server database. Maybe they want you to manage it, troubleshoot it, or learn from it. So they hand you a .MDF file and .LDF file and tell you that you need SQL server 2008 R2 to open them. So you install SQL Server 2008 R2 Express (which, to your relief, is free). You open SQL Server Management Studio(SSMS),...

Read More »

Using SP_MSFOREACHTABLE in sql server for updating common column in all tables

--------------- Query for updating in the existing table common column in all the tables ----------------- EXEC SP_MSFOREACHTABLE' DECLARE @TBLNAME VARCHAR(255); SET @TBLNAME =  PARSENAME("?",1); DECLARE @SQL NVARCHAR(1000); IF EXISTS(  SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME = @TBLNAME AND COLUMN_NAME = ''ISDELETED'' ) BEGIN       SET @SQL = N''ALTER TABLE '' +  @TBLNAME + N'' ALTER COLUMN ISDELETED BIT NOT...

Read More »

Using Top n, WITH TIES, and PERCENT in sql server

DECLARE @TEST TABLE( ID int, Amount int) INSERT INTO @TEST VALUES (1,10),(2,30),(3,60), (4,50),(5,50),(6,70), (7,20),(8,70),(9,70), (10,44),(11,80),(12,90) select * from @TEST select Top 3 ID, Amount from @TEST ORDER BY Amount desc select Top 3 With TIES ID, Amount from @TEST ORDER BY Amount desc select Top 3 PERCENT ID, Amount from @TEST ORDER BY Amount des...

Read More »

Example using merge query in sql server in 2008

-- Create test table and primary key CREATE TABLE test (col1 INT NOT NULL, col2 VARCHAR(100)) go ALTER TABLE dbo.test ADD CONSTRAINT  PK_test PRIMARY KEY CLUSTERED  (  col1  ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,                 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO -- Populate table DECLARE @val INT SELECT @val=1 WHILE @val < 100000 BEGIN    INSERT INTO dbo.test(col1,...

Read More »

Simple site search from sql server stored procedures

GO /****** Object:  StoredProcedure [dbo].[Ameriprise_SP_Get_Data_Search]    Script Date: 08/12/2013 11:24:36 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO Create proc [dbo].[SP_Get_Data_Search]  @SearchText varchar(4000) as begin ( select ac.MenuCode as pageId, ac.Displayname as Title, acd.MetaDescription as [Description], case when ac.ParentMenuCode=0 and ac.Module=0 then 'L1.aspx?menuid='+CAST(ac.MenuCode AS VARCHAR)+'&parentmenuid=0&rootlevel='+CAST(ac.Rootlevel...

Read More »

Drop all stored procedures tables Views in sql server database

/* Drop all non-system stored procs */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) WHILE @name is not null BEGIN     SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'     EXEC (@SQL)     PRINT 'Dropped Procedure: ' + @name     SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name]...

Read More »

TRIGGERS IN SQL SERVER

CREATE TABLE Employee_Test ( Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal Decimal (10,2) ) INSERT INTO Employee_Test VALUES ('Anees',1000); INSERT INTO Employee_Test VALUES ('Rick',1200); INSERT INTO Employee_Test VALUES ('John',1100); INSERT INTO Employee_Test VALUES ('Stephen',1300); INSERT INTO Employee_Test VALUES ('Maria',1400); CREATE TABLE Employee_Test_Audit ( Emp_ID int, Emp_name varchar(100), Emp_Sal decimal (10,2), Audit_Action varchar(100), Audit_Timestamp datetime ) After...

Read More »

Using table value parameter in stored procedure in sql server

Table Valued Parameter In SQL SERVER and .NET (C#): In SQL Server 2005 and earlier, it is not possible to pass a table variable as a parameter to a stored procedure. When multiple rows of data to SQL Server need to send multiple rows of data to SQL Server, developers either had to send one row at a time or come up with other workarounds to meet requirements. While a VB.Net developer recently...

Read More »

A history back to stored procedures in sql server

A complete list what we needed to create a stored procedures ? Creating a database for test : CREATE DATABASE StoredProcDemo GO USE StoredProcDemo GO Create Table Customer ( CustomerID int Identity(1,1) NOT NULL, Name Varchar(100) Not NULL, DateOfBirth DateTime, City Varchar(50), State Varchar(50) ) GO Inserting records in tables: INSERT INTO Customer VALUES('Customer1','06/18/2000','Bangalore','Karnataka') INSERT INTO Customer VALUES('Customer2','06/10/1972','Pune','Maharastra') INSERT...

Read More »

Pagination using sql server database OR Server side paging using sql server

Pagination using sql server database OR Paging from sql server database OR Server side paging using sql server First i am creating a table for testing purpose: CREATE TABLE [dbo].[tblCustomers]( [CustomerID] [nchar](5) NOT NULL, [CompanyName] [nvarchar](40) NOT NULL, [ContactName] [nvarchar](30) NULL, [ContactTitle] [nvarchar](30) NULL, [Address] [nvarchar](60) NULL, [City] [nvarchar](15) NULL, [Region] [nvarchar](15) NULL, [PostalCode] [nvarchar](10) NULL, [Country] [nvarchar](15)...

Read More »

Using XML Path in sql server 2008 for merge duplication rows in one row

Using XML Path with two different SQL function function REPLACE and STUFF or STUFF AND REPLACE FOR XML PATH  String Concatenation or  Using XML PATH MERGE DUPLICATE IN ONE ROW First i am creating a test table: create table tblSubjects (id int identity(1,1), PersonID int, Unit varchar(10)) Inserting record in to the table: insert into tblSubjects values (1,'Che') insert into...

Read More »

Useful sql server user defined functions split function

User defined function :- Split function Creating user defined function for splitting string with any delimiter like comma(,) colon(:) or any special characters separated. CREATE FUNCTION [udf_Split](@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin         declare @idx int         declare @slice varchar(8000)  ...

Read More »