MarshalOfficial’s gists

MarshalOfficial / DataBaseSchemaChangesLog-CreateTableAndTrigger.sql

Last active October 21, 2015 10:24

this trigger log events targeted by It's definition in "DbChangesLog" table ... like create table,alter table, etc ...

	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[DbChangesLog](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[SaveTime] [datetime] NULL,
	[DatabaseName] [varchar](max) NULL,

MarshalOfficial / FTPManager.cs

Created October 21, 2015 10:51

FTPManger class written in c# implements basic ftp site operations like download and upload files and etc...

	using System;
	using System.IO;
	using System.Collections.Generic;
	using System.ComponentModel;
	using System.Data;
	using System.Drawing;
	using System.Text;
	using System.Windows.Forms;
	using System.Net;
	namespace Marshal.Framework.NetworkUtility

MarshalOfficial / SpExecCounts.sql

Created October 21, 2015 11:51

Find the most executed stored procedure(s)(Sp Exec Counts)

	/*###########################################
	Find the most executed stored procedure(s).
	############################################*/
	SELECT DB_NAME(SQTX.DBID) AS [DBNAME] ,
	OBJECT_SCHEMA_NAME(SQTX.OBJECTID,DBID)
	AS [SCHEMA], OBJECT_NAME(SQTX.OBJECTID,DBID)
	AS [STORED PROC] , MAX(CPLAN.USECOUNTS) [EXEC COUNT]
	FROM SYS.DM_EXEC_CACHED_PLANS CPLAN
	CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CPLAN.PLAN_HANDLE) SQTX
	WHERE DB_NAME(SQTX.DBID) IS NOT NULL AND CPLAN.OBJTYPE = 'PROC'

MarshalOfficial / GetCreateTableScriptByTableName.sql

Created October 21, 2015 12:02

this script get create table script of existing table as output

	DECLARE @table_name SYSNAME
	SELECT @table_name = 'dbo.TestExistingTable'

	DECLARE
	@object_name SYSNAME
	, @object_id INT

	SELECT
	@object_name = '[' + s.name + '].[' + o.name + ']'
	, @object_id = o.[object_id]

MarshalOfficial / TablesRowsCountAndSize.sql

Created October 21, 2015 12:05

this script get all tables row count and size as output

	if exists(select * from tempdb..sysobjects where id = OBJECT_ID('#temp_table1'))
	drop table #temp_table1
	GO

	select B.name,CONVERT(numeric(30,3),(CONVERT(float, SUM(A.used_page_count)*8)/1024)) as [Table Used Size(MB)],
	CONVERT(numeric(30,3),(CONVERT(float, SUM(A.reserved_page_count)*8)/1024)) as [Table Located Size(MB)],
	(CONVERT(numeric(30,3),(CONVERT(float, SUM(A.reserved_page_count)8)/1024)) - CONVERT(numeric(30,3),(CONVERT(float, SUM(A.used_page_count)8)/1024))) as [Table Free Size(MB)] ,
	A.row_count
	into #temp_table1
	from sys.dm_db_partition_stats as A, sys.all_objects as B

MarshalOfficial / TableToClass.sql

Created October 21, 2015 12:09

this procedure generate class from a table , output class contains tables columns as variables and generate properties for them

	--exec usp_TableToClass 'Users'
	create PROCEDURE usp_TableToClass --'Users'

	@table_name SYSNAME

	AS

	SET NOCOUNT ON

	DECLARE @temp TABLE

MarshalOfficial / RenuildDatabaseIndexes.sql

Created October 21, 2015 12:22

Rebuild All Indexes In Database

	DECLARE @Database VARCHAR(255)
	DECLARE @Table VARCHAR(255)
	DECLARE @cmd NVARCHAR(500)
	DECLARE @fillfactor INT
	DECLARE @DBName VARCHAR(100)
	select @DBName = 'DBName'

	SET @fillfactor = 90

	DECLARE DatabaseCursor CURSOR FOR

MarshalOfficial / GenerateRandomData.sql

Created October 21, 2015 12:31

this script generate random data as output (random varchar,random datetime,random numeric)

MarshalOfficial / FindUnUsedIndexInDatabase.sql

Created October 21, 2015 12:39

this script list all unused index in database as output

	select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName

	from sys.objects o inner join sys.indexes i

	ON i.[object_id] = o.[object_id] left join

	sys.dm_db_index_usage_stats s

	on i.index_id = s.index_id and s.object_id = i.object_id

MarshalOfficial / Enabled Xp_cmdShell

Created October 21, 2015 12:40

Enabled Xp_cmdShell in Ms Sql Server

	EXEC sp_configure 'allow updates', 0
	RECONFIGURE
	EXEC sp_configure 'show advanced options', 1
	go
	RECONFIGURE
	GO
	EXEC sp_configure 'xp_cmdshell', 1
	GO
	RECONFIGURE
	GO

Reza MarshalOfficial