spaghettidba’s gists

spaghettidba / gist:55c05693f13ce040959f

Created September 25, 2014 11:01

	<html xmlns="http://www.w3.org/1999/xhtml">
	<head />
	<body class="product-list">
	<div class="product">Product Details:
	<span class="locale">en-us</span>
	<span class="name">Help Library</span>
	<span class="description" />
	<img class="icon" src="../../content/image/ic101471" alt="Help 3 Icon" />
	<a class="product-link" href="product1.html">Product Group</a>
	</div>

spaghettidba / gist:2a79d1698b1bc7847df6

Last active August 29, 2015 14:11

SQL Server file used space

	-- This is captured from SSMS (shrink file dialog)

	use [yourDatabase];
	select s.Name, CAST(CASE s.type WHEN 2 THEN s.size * CONVERT(float,8)
	ELSE dfs.allocated_extent_page_count*convert(float,8) END AS float) AS [UsedSpaceKB]
	,CASE s.type WHEN 2 THEN 0
	ELSE 5120 - dfs.allocated_extent_page_count*convert(float,8) END AS [AvailableSpaceKB]
	from sys.filegroups AS g
	inner join sys.database_files AS s on ((s.type = 2 or s.type = 0) and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
	left outer join sys.dm_db_file_space_usage as dfs ON dfs.database_id = db_id() AND dfs.file_id = s.file_id

spaghettidba / [FN_ObtenerArbolEmpresa]

Last active August 29, 2015 14:22

	ALTER function [dbo].[FN_ObtenerArbolEmpresa](@empresaID int)
	RETURNS TABLE
	AS
	RETURN
	WITH temp
	AS (
	-- anchor
	SELECT id_empresa_hijo, id_empresa_padre
	FROM empresa_x_empresa with(nolock)
	WHERE id_empresa_padre = @empresaID

spaghettidba / tlog_backup.sql

Created September 9, 2015 22:59

	USE master;
	GO

	IF DB_ID('TestTlogBackup') IS NOT NULL
	BEGIN
	ALTER DATABASE TestTlogBackup SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE TestTlogBackup;
	END
	GO

spaghettidba / ssms.exe.manifest

Last active August 18, 2016 12:44

ssms.exe.manifest

	<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0" xmlns:asmv3="urn:schemas-microsoft-com:asm.v3">
	<asmv3:application>
	<asmv3:windowsSettings xmlns="http://schemas.microsoft.com/SMI/2005/WindowsSettings">
	<dpiAware>True</dpiAware>
	</asmv3:windowsSettings>
	</asmv3:application>
	<dependency>
	<dependentAssembly>
	<assemblyIdentity type="win32" name="Microsoft.Windows.Common-Controls" version="6.0.0.0" processorArchitecture="X86" publicKeyToken="6595b64144ccf1df" language="*" />

spaghettidba / ForumSurfer.OPML

Created August 31, 2016 10:10

OPML collection of all the feeds I subscribe to in ForumSurfer

	<?xml version="1.0" encoding="utf-8"?>
	<opml version="1.0">
	<head>
	<title>Feeds subscribed in ForumSurfer</title>
	</head>
	<body>
	<outline text="http://dba.stackexchange.com/" title="dba.stackexchange.com">
	<outline type="rss" text="Active questions tagged sql-server or sql-server-2008 or sql-server-2012 or sql-server-2008-r2 or sql-server-2014 - Database Administrators Stack Exchange" title="Active questions tagged sql-server or sql-server-2008 or sql-server-2012 or sql-server-2008-r2 or sql-server-2014 - Database Administrators Stack Exchange" xmlUrl="http://dba.stackexchange.com/feeds/tag/sql-server OR sql-server-2008 OR sql-server-2012 OR sql-server-2008-r2 OR sql-server-2014" />
	</outline>
	<outline text="http://www.sqlservercentral.com/" title="www.sqlservercentral.com">

spaghettidba / dba_ForEachDB.sql

Last active March 7, 2018 10:32

dba_ForEachDB.sql #blog

	-- http://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/
	--
	-- Author: Gianluca Sartori @spaghettidba
	-- Date: 2011/09/09
	--
	-- Description: Executes a statement against multiple databases
	-- Parameters:
	-- @statement: The statement to execute
	-- @replacechar: The character to replace with the database name
	-- @name_pattern: The pattern to select the databases

spaghettidba / dba_RunCheckDB.sql

Last active October 13, 2018 16:01

dba_RunCheckDB.sql #blog

	-- http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/
	-- You have a TOOLS database, don't you?
	-- If not, create it: you'll thank me later.
	USE TOOLS;
	GO

	IF NOT EXISTS( SELECT 1 FROM sys.schemas WHERE name = 'maint')
	EXEC('CREATE SCHEMA maint');
	GO

spaghettidba / sp_Template.sql

Last active March 7, 2018 10:31

sp_Template.sql #blog

	-- http://spaghettidba.com/2011/07/08/my-stored-procedure-code-template/
	-- =============================================
	-- Author: <Author,,Name>
	-- Create date: <Create Date,,>
	-- Description: <Description,,>
	-- =============================================
	CREATE PROCEDURE <ProcedureName, sysname, >
	AS
	BEGIN
	SET NOCOUNT ON;

spaghettidba / verify_script.sql

Last active March 7, 2018 10:33

verify_script.sql #blog

	-- http://spaghettidba.com/2012/03/15/how-to-eat-a-sql-elephant/
	-- =============================================
	-- Author: Gianluca Sartori - spaghettidba
	-- Create date: 2012-03-14
	-- Description: Runs two T-SQL statements and
	-- compares the results
	-- =============================================

	-- Drop temporary tables
	IF OBJECT_ID('tempdb..#original') IS NOT NULL

Gianluca Sartori spaghettidba