Posts Tagged ‘SQLServer’

— Please change the database name according to environment
ALTER DATABASE <DBNAME> SET TRUSTWORTHY ON;
GO
declare @user varchar(50)
SELECT @user = quotename(SL.Name)
FROM master..sysdatabases SD inner join master..syslogins SL
on SD.SID = SL.SID
Where SD.Name = DB_NAME()
exec(‘exec sp_changedbowner ‘ + @user)
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘SP_Name’)
DROP Procedure SP_Name;
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = ‘CLRInterface’)
DROP ASSEMBLY CLRInterface;
go

— Please change the path according environment
CREATE ASSEMBLY CLRInterface¬†FROM ‘C:\AssemblyName.dll’
WITH PERMISSION_SET = UNSAFE;
GO

CREATE Procedure SP_Name(@ConfigName nVarchar(100))
AS EXTERNAL NAME InterfaceName.ClassName.[MethodName];
go

EXEC sp_configure ‘show advanced options’ , ‘1’;
reconfigure;

EXEC sp_configure ‘clr enabled’ , ‘1’ ;
reconfigure;

EXEC sp_configure ‘show advanced options’ , ‘0’;
reconfigure;
GO

Advertisements

— Following query is helpful to find all db objects that used to set value for the given columnname of a table …

select distinct name,o.type
from syscomments c
join sysobjects o on c.id = o.id
where TEXT like ‘%TableName%’ and TEXT like ‘%Set%ColumnName%’
order by name

— Finding table dependencies using SP

sp_depends ‘<Table Name>’

— Below query gives all difference records between old & new tables one below other
(SELECT * FROM Table_Old
UNION
SELECT * FROM Table_New)
EXCEPT
(SELECT * FROM Table_Old
INTERSECT
SELECT * FROM Table_New)

— Below query gives all difference records from old table first and then all difference records from new table
select * from
(
select * from Table_Old
except
select * from Table_New
) as T
union all
select * from
(
select * from Table_New
except
select * from Table_Old
) as T

1)

select *
from (
select
name,(CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4) run_date,run_duration
,(case run_status when 1 then ‘Success’
when 0 then ‘Failed’
when 2 then ‘Retry’
when 3 then ‘Canceled’
end) run_status
,message
from msdb..sysjobhistory h
inner join msdb..sysjobs j on h.job_id = j.job_id
where step_id=0) t
where
run_date >= CONVERT(VARCHAR(10),GETDATE()-1,111)
order by run_date desc

——————

2) sp_who3

3)

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
cast(req.total_elapsed_time / (1000 * 60) as varchar(10)) + ‘ minutes’,
cast(req.total_elapsed_time / (1000 * 60 * 60) as varchar(10)) + ‘+ hours’
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

4)

select distinct
job_name,
run_datetime_original,
run_datetime,
SUBSTRING(run_duration, 1, 2) + ‘:’ + SUBSTRING(run_duration, 3, 2) + ‘:’ + SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select
job_name,
run_datetime run_datetime_original,
DATEADD(hh, -7, run_datetime) as run_datetime,
run_duration = RIGHT(‘000000’ + CONVERT(varchar(6), run_duration), 6)
from
(
select
j.name as job_name,
run_datetime = max(CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4)
from msdb..sysjobhistory h
inner join msdb..sysjobs j on h.job_id = j.job_id
group by j.name
) t
inner join msdb..sysjobs j on t.job_name = j.name
inner join msdb..sysjobhistory h on j.job_id = h.job_id and t.run_datetime = CONVERT(DATETIME, RTRIM(h.run_date)) + (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4
) dt
order by run_datetime desc

— 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/

— 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