<\/p>\n
-- sorting sp_who\r\nCreate Table #temptable\r\n(\r\nspid smallint,\r\necid smallint,\r\nstatus varchar(100),\r\nloginame varchar(100),\r\nhostname varchar(100),\r\nblk smallint,\r\ndbname varchar(100),\r\ncmd varchar(100),\r\nrequest_id smallint\r\n)\r\n\r\nInsert Into #temptable\r\nExec sp_who\r\n\r\nSelect * From #temptable order by [dbname]\r\n\r\ndrop table #temptable\r\n-- end sorting sp_who<\/pre>\n-- what's running\r\nSELECT \/* ignore this *\/ command,\r\n r.session_id, r.blocking_session_id, r.database_id, d.name,\r\n s.text,\r\n start_time,\r\n percent_complete, \r\n CAST(((DATEDIFF(s,start_time,GetDate()))\/3600) as varchar) + ' hour(s), '\r\n + CAST((DATEDIFF(s,start_time,GetDate())%3600)\/60 as varchar) + 'min, '\r\n + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,\r\n CAST((estimated_completion_time\/3600000) as varchar) + ' hour(s), '\r\n + CAST((estimated_completion_time %3600000)\/60000 as varchar) + 'min, '\r\n + CAST((estimated_completion_time %60000)\/1000 as varchar) + ' sec' as est_time_to_go,\r\n dateadd(second,estimated_completion_time\/1000, getdate()) as est_completion_time \r\nFROM sys.dm_exec_requests r\r\ninner join sysdatabases d on r.database_id = d.dbid \r\nCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s<\/pre>\n-- Object ids\/names\r\n\r\nselect OBJECT_ID('[fox].[runproc]')\r\n\r\nselect OBJECT_NAME(213575799)\r\n\r\n-- End Object ids\/names\r\n\r\n<\/pre>\n-- Get strings in DB Code\r\n\r\nSELECT DISTINCT\r\n\t [DBName]=DB_NAME(),\r\n\t o.object_id,\r\n\t [schema_name]=OBJECT_SCHEMA_NAME(o.object_id),\r\n o.name AS Object_Name,\r\n o.type_desc\r\n FROM sys.sql_modules m \r\n INNER JOIN \r\n sys.objects o \r\n ON m.object_id = o.object_id\r\n WHERE m.definition Like '%\\[fox\\].\\[tab1\\]%' ESCAPE '\\';\r\n\r\n\r\nSELECT DISTINCT \r\n o.name AS Object_Name,\r\n o.type_desc\r\n FROM sys.sql_modules m \r\n INNER JOIN \r\n sys.objects o \r\n ON m.object_id = o.object_id\r\n WHERE m.definition Like '%%';\r\n\r\n -- example:\r\n SELECT DISTINCT \r\n o.name AS Object_Name,\r\n o.type_desc\r\n FROM sys.sql_modules m \r\n INNER JOIN \r\n sys.objects o \r\n ON m.object_id = o.object_id\r\n WHERE m.definition Like '%\\[fox\\].\\[tab1\\]%' ESCAPE '\\';\r\n \r\n -- End Get strings in DB Code<\/pre>\n-- check backups\r\nSELECT \r\nCONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, \r\nmsdb.dbo.backupset.database_name, \r\nmsdb.dbo.backupset.backup_start_date, \r\nmsdb.dbo.backupset.backup_finish_date, \r\nmsdb.dbo.backupset.expiration_date, \r\nCASE msdb..backupset.type \r\nWHEN 'D' THEN 'Database' \r\nWHEN 'L' THEN 'Log' \r\nEND AS backup_type, \r\nmsdb.dbo.backupset.backup_size, \r\nmsdb.dbo.backupmediafamily.logical_device_name, \r\nmsdb.dbo.backupmediafamily.physical_device_name, \r\nmsdb.dbo.backupset.name AS backupset_name, \r\nmsdb.dbo.backupset.description \r\nFROM msdb.dbo.backupmediafamily \r\nINNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id \r\nWHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) \r\nORDER BY \r\nmsdb.dbo.backupset.database_name, \r\nmsdb.dbo.backupset.backup_finish_date \r\n-- end check backups\r\n<\/pre>\n-- first day of the month\r\nbegin\r\nDECLARE @Date DATETIME\r\nDECLARE @NowDay DATETIME\r\nDECLARE @FirstDay DATETIME\r\n\r\nset @Date=GetDate()\r\n\r\nset @NowDay=cast(floor(cast(@Date as float)) as datetime)\r\nset @FirstDay=DATEADD(mm, DATEDIFF(mm,0,@Date), 0)\r\n\r\nselect @NowDay\r\nselect @FirstDay\r\n\r\nif (@NowDay = @FirstDay)\r\nbegin\r\n select 'It''s the first day of the month'\r\nend\r\nelse\r\nbegin\r\n select 'It is NOT the first day of the month'\r\nend\r\nend\r\n<\/pre>\n--drop database that is in a 'RESTORING' state\r\n-- make sure it is not required first!\r\n\r\nEXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'TESTDB'\r\nGO\r\nUSE [master]\r\nGO\r\n\r\nDROP DATABASE []\r\nGO\r\n\r\n<\/pre>\n-- Check error logs\r\ndeclare @temp table (LogDate DATETIME, ProcessInfo VARCHAR(50), MessageText VARCHAR(MAX))\r\n \r\n--exec sp_enumerrorlogs\r\n \r\ninsert into @Temp\r\nexec xp_readerrorlog\r\nselect *\r\nfrom (\r\n select top 1000 * from @Temp --where MessageText like '%succ%'\r\n order by LogDate desc\r\n) t\r\norder by LogDate\r\n\r\n<\/pre>\n-- Generate \"CREATE FOR ATTACH statments\"\r\n\r\n SET nocount ON\r\n SET concat_null_yields_null OFF\r\n DECLARE @cmd VARCHAR(1000),\r\n @db sysname,\r\n @a VARCHAR(MAX),\r\n @Filecnt INT,\r\n @cnt INT,\r\n @fileid INT,\r\n @sq CHAR(1),\r\n @dq CHAR(2),\r\n @TempFilename VARCHAR(1000),\r\n @TempFilename1 VARCHAR(1000)\r\n SET @sq = ''''\r\n SET @dq = ''''''\r\n SET @cnt = 1\r\n SET @fileid = 1\r\n IF @db IS NOT NULL\r\n BEGIN\r\n CREATE TABLE #1 (\r\n fileid INT,\r\n filename SYSNAME,\r\n name SYSNAME)\r\n SET @cmd = 'Insert into #1 (fileid,filename,name) select file_id, physical_name, name \r\n from sys.master_files where database_id = db_id('''\r\n + @db\r\n + ''')'\r\n EXEC( @cmd)\r\n SELECT @filecnt = (SELECT COUNT(* )\r\n FROM #1)\r\n 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)\r\n END\r\n SELECT @a = 'CREATE DATABASE '\r\n + @db\r\n + ' ON '\r\n + @a\r\n SELECT @a = (SELECT SUBSTRING(@a,1,LEN(@a)- 1)) \r\n SELECT @a = @a\r\n + '\r\nFOR ATTACH;\r\nGO'\r\n PRINT @a\r\n END\r\n ELSE\r\n BEGIN\r\n DECLARE db_cursor CURSOR FOR\r\n SELECT name\r\n FROM sysdatabases\r\n WHERE name NOT IN ('tempdb','master','msdb','model')\r\n AND dbid NOT IN (SELECT database_id\r\n FROM sys.databases\r\n WHERE state > 0)\r\n ORDER BY dbid\r\n OPEN db_cursor\r\n FETCH NEXT FROM db_cursor\r\n INTO @db\r\n WHILE @@FETCH_STATUS = 0\r\n BEGIN\r\n CREATE TABLE #2 (\r\n fileid INT,\r\n filename SYSNAME,\r\n name SYSNAME)\r\n SET @cmd = 'Insert into #2 (fileid,filename,name) select file_id, physical_name, name \r\n from sys.master_files where database_id = db_id('''\r\n + @db\r\n + ''')'\r\n EXEC( @cmd)\r\n SELECT @filecnt = (SELECT COUNT(* )\r\n FROM #2)\r\n 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)\r\n END\r\n SELECT @a = 'CREATE DATABASE '\r\n + @db\r\n + ' ON '\r\n + @a\r\n SELECT @a = (SELECT SUBSTRING(@a,1,LEN(@a)- 1)) \r\n SELECT @a = @a\r\n + '\r\nFOR ATTACH;\r\nGO\r\n\r\n'\r\n PRINT @a\r\n SELECT @a = ' '\r\n DROP TABLE #2\r\n SET @cnt = 1\r\n SET @fileid = 1\r\n FETCH NEXT FROM db_cursor\r\n INTO @db\r\n END\r\n CLOSE db_cursor\r\n DEALLOCATE db_cursor\r\n END\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"— 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 … Continue reading