Database file properties …

--Database file logical ids, physical location, sizes and growth factors.

select ltrim(+'['+m.name+']')
as database_name, ltrim(rtrim(substring(s.name, 1,200))) as logical_file_name, ltrim(rtrim(s.filename)) as physical_file_path, case
when s.maxsize = -1 then convert(varchar(200), s.maxsize)+space(1)+'[Unlimited File Growth]' else convert(varchar(200), convert(decimal(18,2), s.maxsize/1024*8))+space(1)+'(MB)' end as maximum_allowable_size, case when convert(varchar(200),convert(decimal(18,2),s.size*8/1024)) < = '0' then
'0' else convert(varchar(200),convert(decimal(18,2),s.size*8/1024))+
space(1)+'(MB)' end as current_size, case
when s.status & 0x100000 = 0x100000 then ( select
convert(varchar(200), cast(growth as varchar(10)))+'%' from sysfiles where fileid = s.fileid) else cast(convert(varchar(200), s.growth*8/1024) as
varchar(10))+'(MB)' end as growth_factor, case
when s.status & 0x100000 = 0x100000 then 'Autogrow' else 'Fixed Growth' end as
status from master .. sysaltfiles s inner
join master .. sysdatabases m on m.dbid = s.dbid group
by m.name,s.fileid,s.name,s.filename, s.maxsize,s.size,s.growth,s.status order
by 1

BCP database table data out …

--bcp database data out by generating bcp syntax.

--Ensure the path and schema exists.

declare @cmd varchar(2000)

declare @date varchar(30)

select @date = replace(convert(varchar(11) , getdate(),103),'/','')+replace(convert(varchar(11), getdate(), 108),':','')

select 'exec master..xp_cmdshell' + ' ''' + 'bcp "' + ''+ TABLE_CATALOG + '.'
+ TABLE_SCHEMA + '.' + TABLE_NAME + '" out ' + '"C:\OUT\' +convert(varchar(11),
getdate(),112)+'\'+ TABLE_CATALOG + '_' + TABLE_SCHEMA + '_' + TABLE_NAME +
'_'+@date+'.out"' + ' -N' + ' -T' + ' -S' + @@servername +
''''from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbo'

Database properties …

--Database properties.

use
master go

select convert(varchar(500),'['+ltrim(rtrim(name))+']') as [Name], min(case
status & 8 when 8 then 'X' else 'O' end) as [trunc. log on chkpt], min(case
status & 4 when 4 then 'X' else 'O' end) as [select into/bulkcopy], min(case
status & 1 when 1 then 'X' else 'O' end) as [autoclose], min(case
status & 16 when 16 then 'X' else 'O' end) as [torn page detection], min(case
status & 32 when 32 then 'X' else 'O' end) as [loading], min(case
status & 64 when 64 then 'X' else 'O' end) as [pre recovery], min(case
status & 128 when 128 then 'X' else 'O' end) as [recovering], min(case
status & 256 when 256 then 'X' else 'O' end) as [Ot recovered], min(case
status & 512 when 512 then 'X' else 'O' end) as [offline], min(case
status & 1024 when 1024 then 'X' else 'O' end) as [read only], min(case
status & 2048 when 2048 then 'X' else 'O' end) as [dbo use only], min(case
status & 4096 when 4096 then 'X' else 'O' end) as [single user], min(case
status & 32768 when 32768 then 'X' else 'O' end) as [emergency mode], min(case
status & 4194304 when 4194304 then 'X' else 'O' end) as [autoshrink], min(case
status & 1073741824 when 1073741824 then 'X' else 'O' end) as [cleanly shutdown], min(case status2 & 16384 when 16384 then 'X' else 'O' end) as [ANSI null default], min(case status2 & 65536 when 65536 then 'X' else 'O' end) as [concat null yields null], min(case status2 & 131072 when 131072 then 'X' else 'O' end) as [recursive triggers], min(case status2 & 1048576 when 1048576 then 'X' else 'O' end) as [default to local cursor], min(case status2 & 8388608 when 8388608 then 'X' else
'O' end) as [quoted identifier], min(case status2 & 33554432 when 33554432 then 'X'
else 'O' end) as [cursor close on commit], min(case status2 & 67108864 when 67108864 then 'X' else 'O' end) as [ANSI nulls], min(case status2 & 268435456 when 268435456 then 'X' else 'O' end) as [ANSI warnings], min(case status2 & 536870912 when 6870912 then 'X' else 'O' end) as [full text enabled] from sysdatabases where name is not null group by name order by name

Enumerate management error logs …

--enumerate error logs

exec master .. sp_enumerrorlogs

What date is it …

--first day of the month

select
DATEADD(mm,
DATEDIFF(mm,0,getdate()), 0)
as first_day_of_month

-- first day of the year

select
DATEADD(yy,
DATEDIFF(yy,0,getdate()), 0)
as first_day__of_year

-- first day of the quarter

select
DATEADD(qq,
DATEDIFF(qq,0,getdate()), 0)
as first_day_of_quarter

-- last day of the current month

select
dateadd(ms,-3,DATEADD(mm,
DATEDIFF(m,0,getdate()
)+1, 0))
as last_day_of_current_month

--number of days remaining in current month

select
datediff(dd,
getdate(),
dateadd(ms,-3,DATEADD(mm,
DATEDIFF(m,0,getdate()
)+1, 0)))
as days_remaining_in_current_month

How large is my instance …

--Calculate an instance data mass ...

set nocount off

declare @table table
(dbname varchar(200), dbsize decimal(12,2)) insert into @table select
ltrim(upper(s.name))
as database_name, cast(convert(decimal(12,2),sum(x.size*8/1024))
as varchar(20))
as database_mass from master .. sysaltfiles x inner
join master .. sysdatabases s on s.dbid = x.dbid group
by s.name select
cast ( sum(dbsize)/1024 as varchar(20)) +space(1)+'GB' as overall_mass from @table

How long as SQLAGENT been up & running …

--How long as SQLAGENT been up & running ...

select cast((datediff(hh, login_time, getdate()))/24 as varchar(3)) +
' days and ' + cast((datediff(hh, login_time, getdate())) % 24 as varchar(2)) +
' hours ' + cast((datediff(n, login_time, getdate())) % 60 as varchar(2)) +
' minutes' from master..sysprocesses where program_name like '%AGENT%' and login_time = (select max(login_time) from master .. sysprocesses where program_name like '%AGENT%')

How long as SQL SERVER been up & running …

--How long as SQL Server been up.

select cast((datediff(hh, crdate, getdate()))/24 as varchar(3)) + ' days and ' + cast((datediff(hh, crdate, getdate())) % 24 as varchar(2)) + ' hours ' + cast((datediff(n, crdate, getdate())) % 60 as varchar(2)) + ' minutes' from master..sysdatabases where name = 'tempdb'