Database space used

EXECUTE sp_spaceused ur_table; EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';

Remote Desktop Connection Manager-RDCMan

You could connect and work multiple remote session at once in a single screen!!!. For Download http://www.microsoft.com/en-us/download/details.aspx?id=21101 For demo video http://www.youtube.com/watch?v=qVTAHo-uUAw Kindly think about the security risk before configure all the remote screens at a single file.

xcopy for fast copy

xcopy c:\dbbk\*.* \\tsclient\e\*.* /E /R /K /Y use this method to copy files from remote or other places. It would be faster than other methods.

getting backup details

--query 1
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

--query 2

-- for single db back up history

DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()

-- Get Backup History for required database

SELECT TOP ( 30 )
s.database_name,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ' ' + 'MB' as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
'BackupType'=CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEn 'L' THEN 'Transaction Log'
END ,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date desc,
backup_finish_date

How to compare two tables in databases

/*
use DMNDB
SELECT
OBJ.NAME as TableName into #dmndbTables
FROM
SYSOBJECTS OBJ
WHERE
OBJ.TYPE = 'U'
go
use DMNqa
SELECT
OBJ.NAME as TableName into #dmnqaTables
FROM
SYSOBJECTS OBJ
WHERE
OBJ.TYPE = 'U'

*/
go

select TableName AS dmndbTables FROM #dmndbTables WHERE TableName NOT IN (SELECT TableName FROM #dmnqaTables)

select TableName AS dmnQATables FROM #dmnQATables WHERE TableName NOT IN (SELECT TableName FROM #dmnDBTables)

---------------------
--we can get table columns
CREATE VIEW V_ALL_USERTABLE_COLUMNS
AS
(
SELECT
OBJ.NAME as TableName,
COL.NAME as ColName,
TYP.NAME AS TYPE

FROM
SYSOBJECTS OBJ,
SYSCOLUMNS COL,
SYSTYPES TYP

WHERE
OBJ.TYPE = 'U'
AND OBJ.ID = COL.ID
AND COL.TYPE = TYP.TYPE
)

how to get rows into single field

declare @AccountCategory varchar(max)

set @AccountCategory=''
SELECT @AccountCategory=@AccountCategory+', '+ Prefix FROM(select Prefix
from a_AccPrefixIncludes) AS Prefix

print @AccountCategory

Crystalreport compatability issue fixing

when you create reports with using crystalreport(old version of 8.5), there may be connection issue will come to connect sql2005. you have to run this script to solve this kind of problem.

EXEC sp_dbcmptlevel yourdbname, 80;