tcartwright

-- FIND ALL PRIMARY KEYS WHERE THE FIRST COLUMN IS AN IDENTITY AND THE FILL FACTOR ADDS EMPTY SPACE

SELECT [db_name] = DB_NAME(),
	[schema_name] = OBJECT_SCHEMA_NAME(i.[object_id], DB_ID()),
	[table_name] = OBJECT_NAME(i.[object_id]),  
	[index_name] = [i].Name, 
	[i].[type_desc], 
	[i].[fill_factor], 
	[index_in_row_size_in_mb] = CAST(fn1.[IndexInRowSizeInMB] AS DECIMAL(19,4)),
	[index_in_row_empty_mb] = CAST(fn2.[IndexInRowEmptyMB] AS DECIMAL(19,4)),

tcartwright

	DECLARE @user_name nvarchar(256) = null 

	SET @user_name = ISNULL(@user_name, SUSER_NAME())

	IF @user_name NOT LIKE '%\%' AND NOT EXISTS (SELECT 1 FROM master.sys.server_principals sp WHERE name = @user_name AND sp.[type_desc] = 'SQL_LOGIN') BEGIN
		SET @user_name = DEFAULT_DOMAIN() + '\' + @user_name
	END 

	IF IS_SRVROLEMEMBER('sysadmin', @user_name) = 1 BEGIN
		SELECT UPPER(@user_name) + ' IS SYSADMIN';

tcartwright

############################################################### 
# Eric Ligmans Amazing Free Microsoft eBook Giveaway 
# https://blogs.msdn.microsoft.com/mssmallbiz/2017/07/11/largest-free-microsoft-ebook-giveaway-im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-office-365-office-2016-power-bi-azure-windows-8-1-office-2013-sharepo/
# Link to download list of eBooks 
# http://ligman.me/2sZVmcG 
# Thanks David Crosby for the template (https://social.technet.microsoft.com/profile/david%20crosby/)
# Changes by Tim Cartwright:
#  -exception handling for the header webrequests
#  -grouped titles so as to skip downloading duplicate files
#  -altered path handling to be more stable, and not require ending with a backslash

tcartwright

SELECT n.[Name], o.[type_desc], p.[perms]  
FROM sys.[objects] o
INNER JOIN sys.[schemas] s ON [o].[schema_id] = [s].[schema_id]
CROSS APPLY (SELECT [Name] = QUOTENAME(s.[name]) + '.' + QUOTENAME(o.[name])) n
OUTER APPLY (
	SELECT perms = STUFF((
		SELECT ',' + ip.[permission_name] 
		FROM sys.fn_my_permissions(n.[Name], 'OBJECT') ip
		GROUP BY ip.[permission_name] 
		FOR XML PATH('')

tcartwright

Converted into a module: https://github.com/tcartwright/tcdbtools

PS Gallery link: https://www.powershellgallery.com/packages/tcdbtools/

tcartwright

/*=============================================
	  File: SQL_Server_config_check.sql
 
	  Author: Thomas LaRock, http://thomaslarock.com/contact-me/
	  http://thomaslarock.com/2014/08/sql-server-configuration-check/
 
	  Summary: This script will check the values of your sys.configurations table
	   and compare it to the default values. The script should return a row for any 
	   configuration option that is currently set to a non-default value. 
 

tcartwright

/*
	Author: Tim Cartwright
	1) Leave the @dbname variable empty or null for all databases
	2) Changed the @dbname variable to a specific variable to only get the queries for that database.

	RETURNS: The queries with the highest cost, and longest working time with the worst offenders being at the top of the list.
*/


DECLARE @dbname sysname = '', -- '',

tcartwright

DECLARE @objects TABLE (RecID INT IDENTITY(1,1), name nvarchar(512), type varchar(5))
DECLARE @null_data TABLE (null_data varchar(1))
-- retrieve the list of objects
INSERT INTO @objects(name, type)
	SELECT 
		'[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']', o.type 
	FROM sys.objects o
	WHERE o.is_ms_shipped = 0
		AND (o.type <> 'V' OR OBJECTPROPERTY(o.object_id, 'IsSchemaBound') = 0)
		AND (o.type in ('P', 'V')

tcartwright

IF OBJECT_ID('tempdb..#tbl') IS NOT NULL DROP TABLE #tbl

CREATE TABLE #tbl (
	[DBName] sysname,
	[TableName] sysname,
	[ColumnName] sysname,
	[DataType] sysname
)

EXEC master.dbo.sp_MSforeachdb @command1 = N'

tcartwright

IF OBJECT_ID('tempdb..#CrossDbRefs') IS NOT NULL BEGIN
	DROP TABLE #CrossDbRefs
END

CREATE TABLE [#CrossDbRefs](
	[referencing_database_name] SYSNAME NOT NULL,
	[referencing_object]        SYSNAME NULL,
	[referencing_object_type]   VARCHAR(20) NULL,
	[referenced_database_name]  SYSNAME NULL,
	[referenced_entity_name]    SYSNAME NULL