RENAME DATABASE

sp_renamedb 'Logility_DM','Logility_DM_OLD'

Alter Database Logility_DM_OLD modify file (name = Logility_DM_log, newname = Logility_DM_OLD_log)


DBCC SHRINKFILE ('', EMPTYFILE )

Get Content of store procedure

sp_helptext store_proc_name

http://www.powergui.org/thread.jspa?threadID=8042&tstart=0

or

select m.definition
from sys.objects o
join sys.sql_modules m
on o.object_id = m.object_id
join sys.schemas s
on s.schema_id = o.schema_id
where s.name = 'foo' -- Schema name
and o.name = 'bar' -- Sproc name

or

sys.sql_modules.

PARTITION IN SQL 2005

Although partitioning tables and indexes has always been a design tactic chosen to improve performance and manageability in larger databases, Microsoft SQL Server 2005 has new features that simplify the design. This whitepaper describes the logical progression from manually partitioning data by creating your own tables to the preliminary features, which enabled partitioning through views in SQL Server 7.0 and SQL Server 2000, to the true partitioned table features available in SQL Server 2005. In SQL Server 2005, the new table-based partitioning features significantly simplify design and administration of partitioned tables while continuing to improve performance. The paper's primary focus is to detail and document partitioning within SQL Server 2005 – giving you an understanding of why, when and how to use partitioned tables for the greatest benefit in your VLDB (Very Large Database). Although primarily a VLDB design strategy, not all databases start out large. SQL Server 2005 provides flexibility and performance while significantly simplifying the creation and maintenance of partitioned tables. Review this document to get detailed information about why you should consider partitioned tables, what they can offer and finally how to design, implement, and maintain partitioned tables


http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm

http://www.sqljunkies.com/article/f4920050-6c63-4109-93ff-c2b7eb0a5835.scuk

HOW TO CREATE PARTITION

create database partitiontest on
primary
(
name = db_dat,
filename = 'c:\test\db.mdf',size = 2mb
)
,filegroup fg1
(
name = fg1_dat,filename = 'c:\test\fg1.ndf',size=2mb
)
,filegroup fg2
(
name = fg2_dat,filename='c:\test\fg2.ndf',size = 2mb
)
,filegroup fg3
(
name = fg3_dat,filename='c:\test\fg3.ndf',size = 2mb
)
,filegroup fg4
(
name = fg4_dat,filename='c:\test\fg4.ndf',size = 2mb
)
log on
(
name = db_log,
filename = 'c:\test\log.ndf',size = 2mb,filegrowth = 10%
);

CREATE PARTITION FUNCTION

CREATE PARTITION FUNCTION partfunc (int) AS
RANGE LEFT FOR VALUES(1000,2000)

SELECT * FROM SYS.PARTITION_RANGE_VALUES

CREATE PARTITION SCHEME Partscheme AS
PARTITION partfunc to
(
[fg1],[fg2],[fg3]
)

select * from sys.partition_schemes


CREATE PARTITION TABLE

create table t1
(
id int
,v char(1000) default 'aaaa'
,constraint ci_t1_id primary key clustered (id)
) on partscheme(id);


SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('dbo.t1')

Query partition

SELECT $partition.partfunc(id) as partitionNum,count(*) as NumRows
FROM t1 GROUP BY $Partition.partfunc(id) ORDER BY $Partition.partfunc(id)


SELECT $partition.partfunc(4000) as partitionNum

Get all records in partition 2

SELECT * FROM T1 WHERE $partition.partfunc(id) = 2

HOW TO CREATE PARTITION

create database partitiontest on
primary
(
name = db_dat,
filename = 'c:\test\db.mdf',size = 2mb
)
,filegroup fg1
(
name = fg1_dat,filename = 'c:\test\fg1.ndf',size=2mb
)
,filegroup fg2
(
name = fg2_dat,filename='c:\test\fg2.ndf',size = 2mb
)
,filegroup fg3
(
name = fg3_dat,filename='c:\test\fg3.ndf',size = 2mb
)
,filegroup fg4
(
name = fg4_dat,filename='c:\test\fg4.ndf',size = 2mb
)
log on
(
name = db_log,
filename = 'c:\test\log.ndf',size = 2mb,filegrowth = 10%
);

How do I reset the identity column?

USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 100)
GO

SSIS PACKAGE GENERATOR

http://vulcan.codeplex.com/

http://sqlservermddestudio.codeplex.com/Wiki/View.aspx?title=Quick%20Start&referringTitle=Home