SQL Server Blog by Matt Wiedeman

This code is used to extract files stored in SQL Server

--To extract files from your SQL Server database

-- First,

sp_configure 'show advanced options', 1 
GO 
RECONFIGURE; 
GO 
-- Together with RECONFIGURE, enables Ole Automation system procedures so you can 
-- instantiate OLE objects within Transact-SQL batches.
sp_configure 'Ole Automation Procedures', 1 
GO 
RECONFIGURE; 
GO 
--Displays configured options so you can verify that Ole Automation Procedures is set to 1.
sp_configure 'show advanced options', 1

-- Now, this
USE [TWDEV]
Go --Asia banger!

DECLARE @outPutPath varchar(500) = 'c:\WindowsTEMP\'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max) 
, @folderPath  varchar(max)

--Get Data into temp Table variable so that we can iterate over it

DECLARE @Doctable TABLE (id int identity(1,1), [FileName]  varchar(2000), 
[Doc_Content] varBinary(max) )

INSERT INTO @Doctable([FileName], [Doc_Content])
/*
SELECT substring(pr_addtl_data.s_value,len(dbo.Pathfromfullname(pr_addtl_data.s_value))+2,len(pr_addtl_data.s_value))
,ls_value
FROM data_fields, pr_addtl_data 
WHERE data_fields.id = 84 AND pr_addtl_data.data_field_id = data_fields.id
*/
SELECT pr_addtl_data.s_value,ls_value
FROM data_fields, pr_addtl_data 
WHERE data_fields.id = 449 AND pr_addtl_data.data_field_id = data_fields.id
--select * from @Doctable

SELECT @i = COUNT(1) FROM @Doctable

WHILE @i >= 1

BEGIN

	SELECT
		 @data = [Doc_Content],
		 @fPath = @outPutPath + [FileName],
		 @folderPath = @outPutPath  
		FROM @Doctable WHERE id = @i
  
	  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
	  EXEC sp_OASetProperty @init, 'Type', 1; 
	  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
	  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
	  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
	  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
	  EXEC sp_OADestroy @init; -- Closed the resources
  
	  print 'Document Generated at - '+  @fPath  
 
--Reset the variables for next use

	SELECT @data = NULL 
	, @init = NULL
	, @fPath = NULL 
	, @folderPath = NULL
	SET @i -= 1
END

Good luck!

Use this to check the error log in SQL Server

--Use this to check the error log in SQL Server

-- You can filter out user names or errors

CREATE TABLE #xp_readerrorlog 
(
   LogDate Datetime,  
   ProcessInfo CHAR(15) NULL,  
   Text VARCHAR(max) NULL
) 
GO

INSERT INTO #xp_readerrorlog
EXEC xp_readerrorlog
GO

SELECT *
FROM #xp_readerrorlog
WHERE
text not like '%BACKUP%' and
text not like 'Database backed up%' and
text not like '%found 0 errors and repaired 0 errors%' and
text not like '%This is an informational message only%'
ORDER BY LogDate desc
GO

DROP TABLE #xp_readerrorlog 
GO

Good luck!

This code is used to find your top 10 tables in SQL Server

SELECT
t.name AS TableName,
s.name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
GROUP BY
t.name, s.name, p.rows
ORDER BY
TotalSpaceMB DESC, t.name;

Good luck!

I grabbed this code from a vendor a long time ago. Yes, there are many variations of sp_who out there. I like this one, though.

select EventTime = getdate(),
spid, blocked, 
ElapsedMinutes=datediff(ss,r.last_batch,getdate())/60.0,
waitresource, REPLACE(REPLACE(T.Text,char(10),' '), char(13),' ') as batch,
StmtText = case When b.TxtLen>0 then SUBSTRING(t.[Text],a.Pos, b.TxtLen) Else ' ' End,
DatabaseName = DB_Name(R.dbid),
m.grant_time, m.granted_memory_kb,
m.requested_memory_kb, m.required_memory_kb,
r.cpu, r.physical_io, r.lastwaittype, r.cmd, r.waittime, ex.Status,
p.query_Plan 
FROM sys.sysprocesses R
Left Outer Join sys.dm_exec_query_memory_grants m on m.session_id = r.spid
Left Outer Join sys.dm_exec_requests ex on ex.session_id = r.spid
Outer Apply sys.dm_exec_sql_text(R.sql_handle) t
Outer Apply (select query_plan from sys.dm_exec_query_plan(ex.plan_handle)) p
Outer Apply (select (r.stmt_start / 2) + 1) a(Pos)
Outer Apply (Select case r.stmt_end when -1 Then datalength(T.text) else r.stmt_end END - a.pos) b(TxtLen)
WHERE
1=1
and cmd <> 'AWAITING COMMAND'
and spid <> @@SPID
and spid > 50
and REPLACE(REPLACE(T.Text,char(10),' '), char(13),' ') is not NULL
ORDER BY ElapsedMinutes desc;

Good luck!

This is a nice missing index query just because it builds the create index syntax for you. Who doesn't love that, right?

SELECT db.[name] as [DatabaseName]
	,id.object_id as [ObjectID]
	,OBJECT_NAME(id.[OBJECT_ID])
	,db.[database_id] as ObjectName
	,id.statement as FullyQualifideObjectName
	,id.equality_columns as EqualityColumns
	,id.inequality_columns as InequaltyColumns
	,gs.unique_compiles Uniquecompiles
	,gs.user_seeks as UserSeeks
	,gs.user_scans as UserScans
	,gs.last_user_seek as LastUserSeek
	,gs.last_user_scan as LastUserScan
	,gs.avg_total_user_cost as AvgTotalUserCost
	,gs.avg_total_system_cost as AvgTotalSystemCost
	,gs.avg_system_impact as AvgSystemImpact
	,gs.user_seeks * gs.avg_total_user_cost * (gs.avg_user_impact *.01) as IndexAdvantage
	,'CREATE INDEX [INX_' + OBJECT_NAME(id.object_id, db.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.equality_columns,''),',','_'),'[',''),']','') +
	CASE
	WHEN id.equality_columns is not null AND id.inequality_columns is not null
	THEN '_'
	ELSE ''
	END + REPLACE(REPLACE(REPLACE(ISNULL(id.inequality_columns,''),',','_'),'[',''),']','') +
	+ LEFT(CAST(NEWID() AS [nvarchar](64)),5) + '] ON ' + id.statement + ' (' + ISNULL(id.equality_columns, '') +
	CASE
	WHEN id.equality_columns IS NOT NULL AND id.inequality_columns is not null
	THEN ','
	ELSE ''
	END + ISNULL(id.inequality_columns,'') + ')' + ISNULL (' INCLUDE (' + id.included_columns + ')','') as ProposedIndex
	,CAST(CURRENT_TIMESTAMP as [smalldatetime]) as CollectionDate
FROM sys.dm_db_missing_index_group_stats AS gs WITH (NOLOCK) 
INNER JOIN sys.dm_db_missing_index_groups AS ig WITH(NOLOCK) ON ig.index_group_handle = gs.group_handle 
INNER JOIN sys.dm_db_missing_index_details AS id WITH (NOLOCK) ON ig.index_handle = id.index_handle 
INNER JOIN sys.databases db on db.database_id = id.database_id
WHERE db.[database_id] = DB_ID()
ORDER BY gs.user_seeks * gs.avg_total_user_cost * (gs.avg_user_impact *.01) DESC OPTION (RECOMPILE);

Good luck!