Query to find number of rows in each table

select
 so.name as Table_Name,
 si.rows as Row_count
 from sysobjects as so
 inner join sysindexes as si
 on so.id = si.id
 where so.xtype = 'u'
 and si.indid < 2
 order by so.name



SELECT t.NAME AS TableName, i.name as indexName, sum(p.rows) as RowCounts, sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, (sum(a.data_pages) * 8) / 1024 as DataSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.NAME NOT LIKE 'dt%' AND i.OBJECT_ID > 255 AND i.index_id <= 1 GROUP BY t.NAME, i.object_id, i.index_id, i.name ORDER BY object_name(i.object_id)

Shrink log for all databases

Below query helps to shrink transaction log for all databases

SELECT
      'USE [' + sd.name + N']' + CHAR(13) + CHAR(10)
    + 'DBCC SHRINKFILE (N''' + smf.name + N''' )'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
    sys.master_files smf
    JOIN sys.databases sd
        ON smf.database_id = sd.database_id WHERE smf.file_id=2
and sd.database_id > 4;
 

SQL Server Details

Below query displays the sql server name and product details along with CPU and RAM details
SELECT SERVERPROPERTY('ServerName') AS [SQLServer],

SERVERPROPERTY('ProductVersion') AS [VersionBuild],SERVERPROPERTY('ProductLevel') AS [Product],SERVERPROPERTY ('Edition') AS [Edition],SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly],SERVERPROPERTY('IsClustered') AS [IsClustered],[cpu_count] AS [CPUs],round(cast([physical_memory_in_bytes]/1048576 as real)/1024,2) AS [RAM (GB)]FROM [sys].[dm_os_sys_info]

Get SQL Query from SPID

SELECT sp.spid, sp.blocked, sp.loginame, sp.last_batch, sp.status, sp.hostname, sp.program_name,
(SELECT text FROM sys.dm_exec_sql_text(sp.sql_handle)) AS 'Query'
FROM SysProcesses sp LEft OUTER JOIN sysdatabases sd
on sp.spid = sd.dbid

To know the last update time of a table on a database

--- To know the last update time of a table on a database ---

SELECT OBJECT_NAME(OBJECT_ID) AS Db_Name, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'KALYANDB')
AND OBJECT_ID=OBJECT_ID('AllLog2')

Script to identify users having sysadmin role

SELECT sp.name, sp.type_desc, sp.create_date FROM sys.server_principals sp, sys.server_role_members srm
WHERE sp.principal_id = srm.member_principal_id AND SRM.role_principal_id = 3

Restore HeaderOnly, VerifyOnly, FilelistOnly

Restore Headeronly  -- This command displays backup information of a particular database backup.

Restore Filelistonly  -- This command displays list of data files and log files in the database backup.

Restore VerifyOnly -- Verifies whether the backup set is valid or not, It wont verify the structure of data in the backup media.


SQL Server Syntax

RESTORE FILELISTONLY FROM DISK = 'C:\temp\TestDB.bak'

RESTORE HEADERONLY FROM DISK = 'C:\temp\TestDB.bak'

RESTORE VERIFYONLY FROM DISK = 'C:\temp\TestDB.bak' Redgate Syntax :

Redgate Syntax

EXECUTE master..sqlbackup '-SQL "restore headeronly from disk='C:\temp\TestDB.bak''"'

EXECUTE master..sqlbackup '-SQL "RESTORE filelistonly from disk=''C:\temp\TestDB.bak''"'

EXECUTE master..sqlbackup '-SQL "RESTORE verifyonly from disk=''C:\temp\TestDB.bak''"'

Transfer logins between SQL Servers

We will keep on moving databases between instances, we migrate the databases from one server to another server, or we upgrade the sql versions. All the time we need to move logins from one server to another server. Here is the Microsoft KB article which generates login script, we can directly copy the script and paste it on target server. 

http://support.microsoft.com/kb/918992/

SP_HELP_REVLOGIN is the procedure we need to execute and copy the logins and execute on required server, automatically logins will transfer along with SIDs and passwords.

Script to display indexes

Select so.name as Table_Name, sc.name, si.name as Index_Name, si.type_desc
from sys.objects so, sys.indexes si, sys.columns sc, sys.index_columns sic
where so.object_id = si.object_id and sc.object_id = so.object_id and sic.object_id = so.object_id
and sic.column_id = sc.column_id and sic.index_id = si.index_id
and so.type_desc = 'USER_TABLE' and so.name = 'Address'

Backup / Restore Progress

Select  command, percent_complete from sys.dm_exec_requests where command like 'Backup%'

Select  command, percent_complete from sys.dm_exec_requests where command like 'Restore%'


SELECT command,
            es.text,
            start_time,
            percent_complete
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) es
WHERE er.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')


SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Script to view clustered drives using TSQL

SELECT * FROM fn_servershareddrives()     -- Displays list of attached shared drives

SELECT * FROM sys.dm_io_cluster_shared_drives  -- DMV to display clustered shared drives

Script To Generate Backup of All Databases

DECLARE @db_name VARCHAR(50)

DECLARE @file_path VARCHAR(150)
DECLARE @file_Name VARCHAR(150)

SET @file_path = 'C:\Temp\'

IF left(REVERSE(@file_path ),1) <> '\'
            SET @file_path = @file_path + '\'

DECLARE db_cursor CURSOR FOR
         SELECT name FROM master..sysdatabases WHERE name NOT IN ('tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
             SET @file_Name = @file_path + @db_name + '_' + replace(CONVERT(varchar, getdate(), 
                               101),'/','') + '_Full.BAK'
             BACKUP DATABASE @db_name TO DISK = @file_Name
             FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Script to get space usage information from Data and Log File

Select a.FILEID,

[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15)
from
sysfiles a

Script to get row count for all the tables in all the databases

SET NOCOUNT ON
DECLARE @query VARCHAR(4000)
DECLARE @temp TABLE (DBName VARCHAR(200),TABLEName VARCHAR(300), COUNT INT)
SET @query='SELECT ''?'',sysobjects.Name, sysindexes.Rows
FROM ?..sysobjects INNER JOIN ?..sysindexes ON sysobjects.id = sysindexes.id
WHERE type = ''U'' AND sysindexes.IndId < 2 order by sysobjects.Name'
INSERT @temp
EXEC sp_msforeachdb @query
SELECT * FROM @temp WHERE DBName <> 'tempdb' ORDER BY DBName

Script to display all Databases Sizes, Recovery Model, Free Space etc.,

DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)

DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
(ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate)
EXEC sp_MSForEachDB @command

SELECT
ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate
FROM @DBInfo
ORDER BY
ServerName,
DatabaseName