September 26, 2008

Changing logical file names


Data:
ALTER DATABASE MyDatabase
MODIFY FILE    (NAME = 'RestoredDBname' , NEWNAME =  'TheDBName')
Log:
ALTER DATABASE MyDatabase
MODIFY FILE    (NAME = 'RestoredDBname_Log' , NEWNAME =  'TheDBName_Log')

September 8, 2008

sp_MSforeachtable


There are several undocumented Stored Procedures provided by Microsoft that I have found very useful. Although they come with SQL Server, they are not documented in Books Online.  
The  sp_MSforeachtable  stored procedure resides in the master database, belongs to the sys schema and is used to process T-SQL commands against each table in a database.  

T-SQL example:
USE myDatabase GO
EXEC sp_MSforeachtable @command1='Update Statistics ?'
GO
sp_MSforeachtable syntax:
  exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand
sp_MSforeachtable options:
  • @RETURN_VALUE - return value set by sp_MSforeachtable
  • @command1 - the first command that is processed against all tables. nvarchar(2000)
  • @replacechar - the character that becomes replaced by the table name. The default is '?'
  • @command2 - a second command that can also be processed against all tables. nvarchar(2000)
  • @command3 - a third command that can also be processed against all tables. nvarchar(2000)
  • @whereand - parameter for selecting rows in the sysobjects table. nvarchar(2000)
  • @precommand - parameter for a command to run before all table processing begins. nvarchar(2000)
  • @postcommand - parameter for a command to run after all table processing is completed. nvarchar(2000)
  Execute this T-SQL code to analyze how sp_MSforeachtable works:
  sp_helptext 'sp_MSforeachtable'

September 1, 2008

Replication with identity columns


As a preliminary task for replication, I needed to produce a list of tables with identity columns.
Here's my script:
select * from sys.tables t, sys.columns c
where t.object_id = c.object_id
and c.is_identity = 1 order by t.name
Replication Considerations
I wrote the following query to check whether the "not for replication" parameter had been set. 
I used the object_name function to obtain the table name and additional information about the last value used in the identity column and whether or not it was being used for replication:
select distinct object_name(object_id) as [table], last_value as [value], is_not_for_replication as [replication]
from sys.identity_columns
order by 1
"Not For Replication" parameter
The important thing to remember is that the "not for replication" parameter should be turned on for the identity columns.
This causes the replication agent to replicate the row to the Subscriber with the identity value from the Publisher intact and the identity value will not be reseeded when the row is inserted in the Subscriber table.
sp_identitycolumnforreplication
Fortunately, SQL Server 2005 has many new helpful system procedures including sp_identitycolumnforreplication which sets the replication parameter.
I used it in the following script along with the sp_msforeachtable system procedure to quickly iterate through all of the tables in a database.
EXEC sp_msforeachtable @command1 = ' declare @int int set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'
sp_identitycolumnforreplication is an undocumented stored procedure that is not included in SQL Server 2005 Books Online. It is executed by system processes and the value is set in both the publisher and subscriber when you configure a database for replication.