This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
https://support.microsoft.com/kb/255596?wa=wsignin1.0 | |
*/ | |
USE master | |
GO | |
create procedure sp_lock2 | |
@spid1 int = NULL, /* server process id to check for locks */ | |
@spid2 int = NULL /* other process id to check for locks */ | |
as |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE LOGIN [uefa_play] WITH PASSWORD=N'$AAAAA' | |
CREATE USER uefa_play FROM LOGIN uefa_play; | |
ALTER ROLE dbmanager ADD MEMBER uefa_play; | |
GRANT ALL TO uefa_play; | |
EXEC sp_addrolemember N'db_owner', N'uefa_play' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
s.Name AS SchemaName, | |
t.Name AS TableName, | |
p.rows AS RowCounts, | |
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB, | |
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB, | |
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB | |
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
[sJOB].[job_id] AS [JobID] | |
, [sJOB].[name] AS [JobName] | |
, [sDBP].[name] AS [JobOwner] | |
, [sCAT].[name] AS [JobCategory] | |
, [sJOB].[description] AS [JobDescription] | |
, CASE [sJOB].[enabled] | |
WHEN 1 THEN 'Yes' | |
WHEN 0 THEN 'No' | |
END AS [IsEnabled] |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT DISTINCT DB_NAME(dovs.database_id) DBName, | |
mf.physical_name PhysicalFileLocation, | |
dovs.logical_volume_name AS LogicalName, | |
dovs.volume_mount_point AS Drive, | |
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB | |
FROM sys.master_files mf | |
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs | |
ORDER BY FreeSpaceInMB ASC | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// based on https://msdn.microsoft.com/en-us/library/hh925568%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396 | |
using System; | |
using Microsoft.Win32; | |
namespace NETFWK | |
{ | |
class Program | |
{ | |
static void Main(string[] args) | |
{ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE X; | |
GO | |
ALTER DATABASE X | |
SET RECOVERY SIMPLE; | |
GO | |
DBCC SHRINKFILE (X_log, 1); | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT so.name as TableName, MAX(si.rows) as [RowCount] | |
FROM sysobjects so | |
JOIN sysindexes si ON si.id = OBJECT_ID(so.name) | |
WHERE so.xtype = 'U' | |
GROUP BY so.name | |
ORDER BY [RowCount] DESC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE PROCEDURE sp_who3 | |
( | |
@SessionID int = NULL | |
) | |
AS | |
BEGIN | |
SELECT | |
SPID = er.session_id | |
,Status = ses.status | |
,[Login] = ses.login_name |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE PROCEDURE [dbo].[usp_test] | |
AS | |
BEGIN | |
SET NOCOUNT ON; | |
DECLARE @trancount int; | |
SET @trancount = @@trancount; | |
BEGIN TRY | |
IF @trancount = 0 | |
BEGIN TRANSACTION; | |
ELSE |