-- sorting sp_who
Create Table #temptable
(
spid smallint,
ecid smallint,
status varchar(100),
loginame varchar(100),
hostname varchar(100),
blk smallint,
dbname varchar(100),
cmd varchar(100),
request_id smallint
)
Insert Into #temptable
Exec sp_who
Select * From #temptable order by [dbname]
drop table #temptable
-- end sorting sp_who
-- what's running
SELECT /* ignore this */ command,
r.session_id, r.blocking_session_id, r.database_id, d.name,
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
inner join sysdatabases d on r.database_id = d.dbid
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
-- Object ids/names
select OBJECT_ID('[fox].[runproc]')
select OBJECT_NAME(213575799)
-- End Object ids/names
-- Get strings in DB Code
SELECT DISTINCT
[DBName]=DB_NAME(),
o.object_id,
[schema_name]=OBJECT_SCHEMA_NAME(o.object_id),
o.name AS Object_Name,
o.type_desc
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like '%\[fox\].\[tab1\]%' ESCAPE '\';
SELECT DISTINCT
o.name AS Object_Name,
o.type_desc
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like '%%';
-- example:
SELECT DISTINCT
o.name AS Object_Name,
o.type_desc
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like '%\[fox\].\[tab1\]%' ESCAPE '\';
-- End Get strings in DB Code
-- check backups
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
-- end check backups
-- first day of the month
begin
DECLARE @Date DATETIME
DECLARE @NowDay DATETIME
DECLARE @FirstDay DATETIME
set @Date=GetDate()
set @NowDay=cast(floor(cast(@Date as float)) as datetime)
set @FirstDay=DATEADD(mm, DATEDIFF(mm,0,@Date), 0)
select @NowDay
select @FirstDay
if (@NowDay = @FirstDay)
begin
select 'It''s the first day of the month'
end
else
begin
select 'It is NOT the first day of the month'
end
end
--drop database that is in a 'RESTORING' state
-- make sure it is not required first!
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'TESTDB'
GO
USE [master]
GO
DROP DATABASE []
GO
-- Check error logs
declare @temp table (LogDate DATETIME, ProcessInfo VARCHAR(50), MessageText VARCHAR(MAX))
--exec sp_enumerrorlogs
insert into @Temp
exec xp_readerrorlog
select *
from (
select top 1000 * from @Temp --where MessageText like '%succ%'
order by LogDate desc
) t
order by LogDate
-- Generate "CREATE FOR ATTACH statments"
SET nocount ON
SET concat_null_yields_null OFF
DECLARE @cmd VARCHAR(1000),
@db sysname,
@a VARCHAR(MAX),
@Filecnt INT,
@cnt INT,
@fileid INT,
@sq CHAR(1),
@dq CHAR(2),
@TempFilename VARCHAR(1000),
@TempFilename1 VARCHAR(1000)
SET @sq = ''''
SET @dq = ''''''
SET @cnt = 1
SET @fileid = 1
IF @db IS NOT NULL
BEGIN
CREATE TABLE #1 (
fileid INT,
filename SYSNAME,
name SYSNAME)
SET @cmd = 'Insert into #1 (fileid,filename,name) select file_id, physical_name, name
from sys.master_files where database_id = db_id('''
+ @db
+ ''')'
EXEC( @cmd)
SELECT @filecnt = (SELECT COUNT(* )
FROM #1)
WHILE @cnt <= @filecnt BEGIN SELECT @TempFileName = filename FROM #1 WHERE fileid = @fileid SELECT @TempFileName = RTRIM(@TempFileName) SELECT @a = @a + CHAR(13) + CHAR(9) SELECT @a = @a + '(Filename = ' + @sq + @TempFilename + @sq + '),' SET @cnt = @cnt + 1 SET @fileid = (SELECT MIN(fileid) FROM #1 WHERE fileid > @fileid)
END
SELECT @a = 'CREATE DATABASE '
+ @db
+ ' ON '
+ @a
SELECT @a = (SELECT SUBSTRING(@a,1,LEN(@a)- 1))
SELECT @a = @a
+ '
FOR ATTACH;
GO'
PRINT @a
END
ELSE
BEGIN
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ('tempdb','master','msdb','model')
AND dbid NOT IN (SELECT database_id
FROM sys.databases
WHERE state > 0)
ORDER BY dbid
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #2 (
fileid INT,
filename SYSNAME,
name SYSNAME)
SET @cmd = 'Insert into #2 (fileid,filename,name) select file_id, physical_name, name
from sys.master_files where database_id = db_id('''
+ @db
+ ''')'
EXEC( @cmd)
SELECT @filecnt = (SELECT COUNT(* )
FROM #2)
WHILE @cnt <= @filecnt BEGIN SELECT @TempFileName = filename FROM #2 WHERE fileid = @fileid SELECT @TempFileName = RTRIM(@TempFileName) SELECT @a = @a + CHAR(13) + CHAR(9) SELECT @a = @a + '(Filename = ' + @sq + @TempFilename + @sq + '),' SET @cnt = @cnt + 1 SET @fileid = (SELECT MIN(fileid) FROM #2 WHERE fileid > @fileid)
END
SELECT @a = 'CREATE DATABASE '
+ @db
+ ' ON '
+ @a
SELECT @a = (SELECT SUBSTRING(@a,1,LEN(@a)- 1))
SELECT @a = @a
+ '
FOR ATTACH;
GO
'
PRINT @a
SELECT @a = ' '
DROP TABLE #2
SET @cnt = 1
SET @fileid = 1
FETCH NEXT FROM db_cursor
INTO @db
END
CLOSE db_cursor
DEALLOCATE db_cursor
END