Copy database diagrams from one database to another database in sqlserver2005

Generally we face a situation when we need to move the one Database diagram to another Database. Suppose you have created a database A which contains some database diagram and there is the same or different database B which does not contains any database diagram. Now you have to move only the database diagram of A to the B.

The entire diagram’s information is stored in the dbo.sysdiagrams.

If you wish to see the table dbo.sysdiagrams which is in System Table folder of A,then write the following statement

select * from A.dbo.sysdiagrams

It will list all the diagram information.

Now if you need to move or copy the all the diagrams of database A to the B then write the following sql statement and run:

insert into B.dbo.sysdiagrams
select [name], principal_id, version,definition from A.dbo.sysdiagrams

It will copy all the diagrams of database A to the database B

insert into B.dbo.sysdiagrams
select [name],diagram_id , version,definition from A.dbo.sysdiagrams
where diagram_id =13

Paging with Sqlserver2005

Some time we need to implement the paging concept for our web and windows application. To achieve this we generally do very completed code or we have to do the paging with our controls such as Datagrid, Gridview etc. However this control s may affect the performance of our application. For example if there are 1000000 records and if we implement the paging then we have to do the server site code or if we fetch whole the record from database to the application then it will affect the performance, and if we use web application then we have to very much care about the performance of the application. But don’t worry friends there a very simple procedure to implement the paging through the database. We can filter the recodes in our procedure and then we can fetch only those records what we need.

1. Using the Employees table of northwind

2. Now create a procedure

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Pankaj Saha

-- Create date:

-- Description: To fetch the records by paging

-- =============================================

CREATE PROCEDURE GetEmployees

@CurrentPageNo int

,@PageSize int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

WITH EmployeeCTE

AS

(

select *, ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNumber FROM Employees

)

SELECT * FROM EmployeeCTE WHERE RowNumber between ((@CurrentPageNo-1)*@PageSize )+ 1and (@CurrentPageNo*@PageSize-1)

END

GO

3. Execute the following statement

execute GetEmployees 1 ,10

The output would show the top 10 employees information, if you run

execute GetEmployees 2 ,10

The output would be the next 10 records.