Posts Tagged ‘ SQL Server ’

Rename SQL Server 2005 Database and Datafile

Some of this SQL Commands don’t work in SQL Server 2000.


use master;

ALTER DATABASE Cheque_Req MODIFY FILE (NAME =Cheque_Req_Data, FILENAME = 'C:\ABC.MDF')
ALTER DATABASE Cheque_Req MODIFY FILE (NAME =Cheque_Req_Log, FILENAME = 'C:\ABC.LDF')


ALTER DATABASE Cheque_Req MODIFY FILE (NAME =Cheque_Req_Data, NEWNAME = 'cib')
ALTER DATABASE Cheque_Req MODIFY FILE (NAME =Cheque_Req_Log, NEWNAME = 'cib_log')

alter database Cheque_Req set offline;
-- copy existing data and log file and placed in new desired location
alter database Cheque_Req set online;

ALTER DATABASE Cheque_Req Modify Name = cibDB; 

Thanks
A Rahim Khan

SQL to find out and disable child tables referencing parent table in SQL Server

You can use this query to find out child tables referencing parent Table.

 
--replace 't_User'with your table name
--query to find out which table have references
select
  so.name 'foreign key name',
  OBJECT_NAME(parent_obj) 'table',
  OBJECT_NAME(sf.fkeyid) 'referencing table',
  sc1.name 'referencing column',
  OBJECT_NAME(sf.rkeyid) 'referenced table', 
  sc2.name 'referenced column'
from sysobjects so
inner join sysforeignkeys sf on so.id = sf.constid
inner join syscolumns sc1 on sf.fkeyid = sc1.id and sf.fkey = sc1.colid
inner join syscolumns sc2 on sf.rkeyid = sc2.id and sf.fkey = sc2.colid
where so.xtype in ('F','PK')
and OBJECT_NAME(sf.rkeyid) = 't_User'

Continue reading

Preventive measures with Delete or Update Command in SQL Server 2005

Though it’s not wise trying to run Delete or Update command in any live database, sometimes situations compel to do so. Truly speaking, this is the most dangerous task. You may end up deleting or updating all of your data. For example


DELETE FROM t_BillMaster
WHERE BillMasterID =1;

Continue reading

How to retrieve Inserted, Updated, and Deleted ROW IDENTITY for Trigger in SQL Server 2005

It is very simple to retrieve or select Row Identity or other Columns of inserted, updated or deleted Row while writing a Trigger.


CREATE  TRIGGER tr_EmployeeInsert
ON t_Employee
AFTER INSERT AS
BEGIN

DECLARE @InsertedID AS INT;
SELECT @InsertedID =  EmployeeID FROM INSERTED;
--Now you have the INSERTED Row ID

END

Continue reading

Case Sensitive String Comparison in SQL Server

SQL Server is installed with default “Collation” if it is not specified. You can check out your Database Server default “Collation” using this query.


SELECT SERVERPROPERTY ('Collation');

Continue reading

SQL Server 2005 Database Log File

You must take in to account those key points before trying Database Log Backup.

# Set Database RECOVERY to FULL.
# Take full BACKUP of the Database.
# Now, you can proceed for Database Log BACKUP.
Continue reading

Windows Authentication in SQL Server 2005

Few days earlier, I came to learn some very basic login stuffs while working on Windows Authentication Mode of SQL Server. My task was to disable the Windows Authentication Mode. This is really very simple but it may irritate you if you are not informed of following facts.

First of all, you can’t disable or drop [BUILTIN/Administrators] or [ComputerName/UserName] Windows Credentials if you are logged in to SQL Server using this account.
Continue reading