Skip to content

Instantly share code, notes, and snippets.

View spaghettidba's full-sized avatar

Gianluca Sartori spaghettidba

144 followers · 3 following
View GitHub Profile
@spaghettidba
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
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
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
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
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
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
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
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
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
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