Archive for July, 2012

— Query to see all available foreign keys in the DB
SELECT
f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName,
f.is_disabled
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
where
f.name = ‘<FK Name>’

— Disable foreign key
ALTER TABLE <Table Name> NOCHECK CONSTRAINT <FK Name>
— Enable foreign key
ALTER TABLE <Table Name> CHECK CONSTRAINT <FK Name>

More references …

http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-with-foreign-key-constraint-in-database/

http://www.mssqltips.com/sqlservertip/1376/disable-enable-drop-and-recreate-sql-server-foreign-keys/

Rebuild all indexes on one table …

ALTER INDEX ALL ON <Table Name> REBUILD

Following link has code to rebuild all indexes on all tables of a DB …

http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

mstsc /admin /V: IP

— See the logical db files info for the backup file
RESTORE FILELISTONLY FROM DISK = N’F:\DBBackup\DB_local.bak’ With file = 1

— Restore DB to different hard drive location (existing logical data/log name need to be mapped to new mdf/ldf file names in the new location)
RESTORE DATABASE MYDB1 FROM DISK = N’F:\DBBackup\DB_local.bak’
WITH MOVE N’MYDB_Data’ TO N’E:\SQL\Data\DB.mdf’,
MOVE N’MYDB_Log’ TO N’E:\SQL\Log\DB_log.ldf’,
MOVE N’MYDB_Log1′ TO N’E:\SQL\Log\DB_Log1.ldf’
GO

 

Following sql is helpful to see the DB restore status from time to time. Especially DB’s that take longer time to restore and when sql server management studio is not showing proper status then this query is very handy to check the status.

SELECT
percent_complete AS [PercentComplete]
,estimated_completion_time/1000.0/60.0 AS [RemainingMinutes]
,total_elapsed_time/1000.0/60.0 AS [ElapsedMinutes]
,(estimated_completion_time+total_elapsed_time)/1000.0/60.0 AS [TotalMinutes]
,DATEADD(MILLISECOND, estimated_completion_time, GETDATE()) AS [EstimatedTimeOfCompletion]
,st.TEXT AS [CommandSQL]
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) st
WHERE command LIKE ‘%RESTORE DATABASE%’

http://superuser.com/questions/44106/is-there-a-way-to-auto-login-in-putty-with-a-password

C:\Windows\System32\putty.exe uid@IP -pw pwd

Shelving in Perforce – Link

Posted: July 6, 2012 in Perforce
Tags: ,

http://filehost.perforce.com/downloads/media/shelving/shelving.html