SQL - auto-mate/CheatSheetWiki GitHub Wiki
Make text file
Rename .udl
Double Click File
Enter Details
Test Connection
If OK click OK else edit
Open with Notepad and extract string
- Add and Delete User
- Add Users own private schema
- @ At Variable
- Accounts
- AD and LDAP
- Add Auto Increment
- ALTER TABLE
- APPEND
- BCP SQLSERVER
- Backup
- CASE (switch)
- CREATE
- CROSS APPLY
- CUBE
- Cursor Example
- Compare two tables
- Connecting different Collations
- CONVERT
- DELETE
- DROP
- DTEXEC.EXE
- Encryption Simple
- EXAMPLE ADD ROLE AND EXCEC AS OTHER USER
- Exists
- Excel Connection
- Extended Events
- Extended Properties
- FORMAT
- Grant Revoke Permissions
- Home Network
- FirstLast
- Grouping Sets
- Hash
- Lag
- Last Login Change Date
- Last Use Of Table Index
- LDAP and AD
- Lead
- Limit Acceptable Entries
- Log File Shrink
- Logins see Orphaned Accounts
- In
- Indexing
- Maintenance
- MAKE TABLE
- Merge
- Missing Index
- Move Users
- SET NOEXEC ON
- Openrowset
- OTHER
- Offset / Pagination
- OpenQuery
- Oracle via SQL Server
- Orphaned Accounts
- Parameter Query on the fly
- Password Change
- PERFORMANCE
- Permissions
- Pivot
- Procedures
- Read Write VarBinary To and From File
- Recursive Queries
- Regex Type Searches
- Rename Check Dependencies
- Rename Database
- Rename Table
- Resource Governor
- Reset Increment Counter
- Rollup
- Rollback
- ROW NUMBER SELECTION
- Running Totals
- SELECT
- Single User
- SQLCMD
- SQL To Text File
- SQLCOMMAND MODE
- SYSTEM DATA
- TEMPORARY TABLES
- Trace
- Transaction
- Transaction and Rollback inc cursor
- TRIGGERS
- Tuning
- UPDATE
- UNION
- User Permissions
- Variables
- View Views
- WHILE LOOP
- Wonderfull With
- XML Read
- XPCmdShellEnabled
DECLARE @MyVar int
SET @MyVar=20
SELECT @MyVar
At Variable cont... getting var from SQL QUERY (Example gets last user update time)
DECLARE @LU datetime;
SELECT
@LU = s.last_user_update
FROM
sys.tables t
inner join
sys.dm_db_index_usage_stats s
on
t.object_id = s.object_id
where t.name = '<SOME-TABLE-NAME>'
/* SHOW VAR FROM SELECT STATEMENT*/
SELECT @LU
/*
SQL SERVER MULTI LINE COMMENT
*/
USE database;
CREATE TABLE database.dbo.table (
-- Optional If autoincrement is required (also made primary key here)
ID int IDENTITY(1,1) PRIMARY KEY, -- add ID as autocount field and Primary Key (No Dups)
-- Required Field Names and Types
[FieldNameX] Varchar(MAX),
[FieldNameY] Varchar(MAX),
-- Optional ONE primary key statement if not used above
-- PRIMARY KEY ([ID],[FieldNameX],[FieldNameY]) -- Mix of these 3 fields can only appear once
-- PRIMARY KEY ([ID],[FieldNameX]) -- Mix of these 2 fields can only appear once
)
/* FIELD TYPES
bigint numeric
bit smallint
decimal smallmoney
int tinyint
money
float real
Date and time
date datetimeoffset
datetime2 smalldatetime
datetime time
char (fixed width - non unicode) varchar (var width - non unicode)
text
nchar (fixed width - unicode) nvarchar (var width - unicode)
ntext
binary varbinary
image
cursor rowversion
hierarchyid uniqueidentifier
sql_variant xml
Spatial Geometry Types Spatial Geography Types
table
Drop a Procedure
drop PROCEDURE ProcedureName;
Create a Procedure
CREATE PROCEDURE ProcedureName
@Param1 varchar(max) = '0',
@Param2 varchar(max) = '0'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO databaseName.dbo.table ([Field1],[Field2] ) SELECT @Param1 as A, @Param2 as B
END
GO
Run a Procedure with 1 Parameter Set AND One using Default Value
DECLARE @Param1 varchar(max)='50';
EXECUTE ProcedureName @Param1;
Run Procedure from Command Line
sqlcmd -S <Server\Address> -E -Q "DECLARE @Param1 varchar(max)='100';EXECUTE databaseName.dbo.ProcedureName @Param1"
sqlcmd -S <Server\Address> -E -d <SOME_DATABASE> -Q "exec <SOMEPROC> @variable=$(SOME_PARAM)" /v <SOME_PARAM>=<SOME_VAL>
-- SQL SERVER TYPE [includes conversion from "uk date text" to "date" AND adds CURRENT Timestamp
USE Database
INSERT INTO database.dbo.tableInto ([Field1_Name], [Field2_Name],.......[Fieldn_Name],[TimestampField])
SELECT [tmpField1_Name], [tmpField2_Name],CONVERT(date,[9],103)..........[tmpFieldn_Name],CURRENT_TIMESTAMP
FROM database.dbo.tableFrom
INSERT INTO database.dbo.table ( Field1,Field2 )
SELECT m AS Expr1,n AS Expr2;
-- HERE VALE SET TO PREVIOUS VALUE WITH " REMOVED (CAN BE ONE QUERY)
USE Database
GO
UPDATE [database].[dbo].[table] SET [FieldX]=REPLACE([FieldX],'"','')
UPDATE [database].[dbo].[table] SET [FieldY]=REPLACE([FieldY],'"','')
UPDATE
<AliasName>
SET
<FldToUpdate> = <JoinTable>.<FieldToGetNewValFrom>
FROM
<Table> <AliasName>
INNER JOIN
(SELECT * FROM <JoinTable> WHERE <JoinClause>) <JoinAlias>
ON
<AliasName>.<JoinFieldName> = <JoinAlias>.<JoinFieldName>
AND <AliasName>.<JoinFieldName> = <JoinAlias>.<JoinFieldName>
WHERE
<SomeClause>
ROLLBACK
USE Database
DELETE FROM [database].[dbo].[table] WHERE [FieldX] IS NULL OR [FieldY] IS NULL OR [FieldZ]='string'
USE Database
DROP TABLE [database].[dbo].[table]
SELECT 1 AS Field1,'String' AS Field2 INTO NEW;
SELECT STATEMENT1
UNION ALL
SELECT STATEMENT2
Basic SQL Server IF
IF 1=2
BEGIN
SELECT * FROM [databaseName].[dbo].[Table1];
SELECT '1';
END;
ELSE
BEGIN
SELECT * FROM [databaseName].[dbo].[Table2];
SELECT '2';
END;
SELECT 'OK';
In a batch file using sqlcmd
SET TEMPTABLE=table1
SET SQL=USE [databaseName];
SET SQL=%SQL% if (EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '%TEMPTABLE%')) DROP TABLE [databaseName].[dbo].%TEMPTABLE%;
sqlcmd -S SERVER\SQLINSTANCE -E -Q "%SQL%"
In SQL
USE [databaseName];
if (EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'table1')) DROP TABLE [databaseName].[dbo].[table1];
Un-indexable Field LOB Types
NB N Prefix = Unichar (use for multi language support)
- TEXT
- NTEXT
- IMAGE
- VARCHAR(MAX)
- NVARCHAR(MAX)
- FILESTREAM
- XML
- VARBINARY
Drop_Existing to Rebuild
-- Create nonclustered index for table or view
CREATE INDEX myIndex ON table1 (field1);
-- Create nonclustered unique index
-- multi column and sort order
CREATE UNIQUE INDEX myIndex ON table1 (Field1 DESC, Field2 ASC);
Drop Index to Remove
DROP INDEX myIndex
ON table1;
GO
Rebuild Index
ALTER INDEX <idxName>||<ALL> ON <tblName> REBUILD
Rebuild all in a database
USE <DatabaseName>
GO
DECLARE @ex_str Varchar(max)
DECLARE @id_name Varchar(max)
DECLARE @tbl_name Varchar(max)
DECLARE my_cursor CURSOR FOR
SELECT I.name,T.name FROM
<DatabaseName>.sys.tables T,
<DatabaseName>.sys.indexes I
WHERE
T.object_id = I.object_id
AND I.type IN (1,2)
AND T.type = 'U'
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @id_name,@tbl_name
WHILE @@FETCH_STATUS=0
BEGIN
SET @ex_str = 'ALTER INDEX [' + @id_name + '] ON [' + @tbl_name + '] REBUILD'
EXEC(@ex_str)
FETCH NEXT FROM my_cursor INTO @id_name,@tbl_name
END
CLOSE my_cursor
DEALLOCATE my_cursor
See pagination
e.g. Rows 11 to 20
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY [anyfield]) AS RW,* FROM [database].dbo.[table])
WITHROWCOUNTER
WHERE
RW > 10 and RW <=20
Reset Count using Partition (Both give the same result)
SELECT ROW_NUMBER() OVER (Partition By [field1] ORDER BY [field2] ) AS X,* FROM [database].dbo.[table]
SELECT RANK() OVER (Partition By [field1] ORDER BY [field2] ) AS X,* FROM [database].dbo.[table]
/* LOOP THROUGH 4 SHOPS*/
/*#####################*/
DECLARE @Shop int;
DECLARE @N int;
SET @N=1;
WHILE @N< 5
BEGIN
IF @N=1
BEGIN
SET @Shop='Company';
END
IF @N=2
BEGIN
SET @Shop='Bakery';
END
IF @N=3
BEGIN
SET @Shop='Butchers';
END
IF @N=4
BEGIN
SET @Shop='Clothes';
END
INSERT INTO [Reporting].[dbo].[Sales] (Shop,value)
SELECT Sum(sales) as [value], FROM TAKINGS WHERE [TakingsType] = @Shop ;
SET @N=@N+1;
END
SELECT
CASE [SHOP NAME]
WHEN 'Bakers' Then 'Food'
WHEN 'Butchers' Then 'Food'
WHEN 'Clothes' Then 'Other'
END AS [GroupIng]
OR
SELECT
CASE
WHEN [SHOP NAME] IN ('Bakers', 'Butchers') Then 'Food'
WHEN [SHOP NAME] IN ('Clothes') Then 'Other'
END AS [GroupIng]
Import All RECORDS TO SQL SERVER
using -T (Windows Authentication)
using -t , (comma delimiter)
using -c (character not native transfer)
using -e C:\temp\temp.log (errors to log file)
bcp MyDatabase.dbo.MyTable in c:\temp\FileForImport.csv -S MY_SERVER_NAME\SQLEXPRESS -T -t , -c -e C:\temp\temp.log
SELECT CONVERT(date,'01/12/2017',103)
Gives 2017-12-01
SELECT CONVERT(datetime,'01/12/2017',103)
Gives 2017-12-01 00:00:00.000
SELECT CONVERT(int,'101')*2
Gives 202
SELECT CONVERT(float,'10.1')*2
Gives 20.2
SELECT FORMAT(1000.12345,'#,###.00')
Gives 1,000.12
SELECT FORMAT(CURRENT_TIMESTAMP,'yyyy-MM-dd')
Gives 2020-12-31 If its 31/12/2020
Record a password as a SHA256 Hash with salt
select convert(sql_variant,HASHBYTES('SHA2_256','salt' +'password'))
Run an SQL Query from the command line [-S = SERVER; -E = windows credentials -Q = Run Query and Exit]
sqlcmd -S SERVER_NAME\SQLEXPRESS -E -Q "DELETE FROM [database].[dbo].[table]"
Example from Northwind listing. Taken from mssqltips.com. The final query is one query called Managers. It is made from 2 queries. Query 1 the initial query being the top of the hierarchy or ROOT [Reports to = Null]. Query 2 The recursive bit, this links the data to the current position of the Managers file and loops until nor more data is returned. Known as [CTE Common Table Expression Queries].
WITH Managers AS
(
--initialization
SELECT EmployeeID, LastName, ReportsTo
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
--recursive execution
SELECT e.employeeID,e.LastName, e.ReportsTo
FROM Employees e INNER JOIN Managers m
ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers
e.g. Change Column Type
ALTER TABLE [databaseName].[dbo].[tableName]
ALTER COLUMN [columnName] [nchar](3) NULL
Change Column Name
ALTER TABLE [TableName] RENAME COLUMN [OldColName] TO [NewColName]; /* NOT WORKING ! */
exec sp_rename 'dbName.dbo.tblName.FieldName','newFieldName','COLUMN' /* SEEMS OK */
ADD Column
ALTER TABLE [Test].[dbo].[Test]
ADD [New Col Name] float/varchar(max) etc..
ADD Computed Column
ALTER TABLE [Test].[dbo].[Test]
ADD [New Col Name] as Colx*Coly
NB add PERSISTED (after Colx*Coly) to hold the value as text else it is calculated each time used.
Remove Column
ALTER TABLE [databaseName].[dbo].[tableName]
DROP COLUMN [columnName]
Simple Backup (NB Can use shrink option if required). COPY_ONLY stops it interfering with incremental backups.
NB Add temporary a location with Write Access to [Everyone] if permissions error.
-- For Backup
-------------
BACKUP DATABASE [databasename]
TO DISK = 'C:\backupPath\filename.bak'
WITH
COMPRESSION,
COPY_ONLY ,
NOFORMAT,
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
--NB May Need to change write access on C:\backupPath\
------------------------------------------------------
-- For Restore [Can be on different server]
-------------------------------------------
RESTORE DATABASE [databasename] FROM DISK = 'C:\path\filename.bak'
--MORE RESTORE DETAILED VERSION
-------------------------------
--RUN FIRST TO GET Logical Names And FileNames
----------------------------------------------
RESTORE FILELISTONLY FROM DISK = N'\\path...to...\BackupFile.bak'
--RUN SECOND TO GET COPY TO DATABASE DEFAULT LOCATIONS (DATA/LOG)
-----------------------------------------------------------------
SELECT
SERVERPROPERTY('InstanceDefaultDataPath') AS 'Data Files',
SERVERPROPERTY('InstanceDefaultLogPath') AS 'Log Files'
--RUN WITH DATA FROM ABOVE
--------------------------
--NB MOVE LogicalName and Filename from FILELISTONLY SECTION above.
--TO PATH from Item on DB data being put on
RESTORE DATABASE [<dbname>] FROM DISK = N'\\path...to...\BackupFile.bak'
WITH MOVE '<Logical Name Data>' TO '\\newserverpath\<datafilename>',
MOVE '<Logical Name Log>' TO '\\newserverpath\<logfilename>'
DECLARE @TEST Varchar(max)
DECLARE my_cursor CURSOR FOR SELECT * FROM MyTable
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @TEST
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @TEST;
FETCH NEXT FROM my_cursor INTO @TEST
END
CLOSE my_cursor
DEALLOCATE my_cursor
Example 2
Dynamic SQL Finds Tables with field called 'Updated' Loops through writing Table Name to @TBLNAME.
Creates SQL Text that gets Max 'Updated' from Server1 and Server2. If Server 1 'Update' is newer then
prompts for Copy Required and Names Table.
DECLARE @TBLNAME varchar(max);
DECLARE @SQLTXT varchar(max);
DECLARE my_cursor CURSOR FOR select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='Updated' GROUP BY TABLE_NAME
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @TBLNAME;
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQLTXT=
'DECLARE @SVR1 datetime;
DECLARE @SVR2 datetime;
SET @SVR1 =(SELECT MAX([Updated]) FROM '+ @TBLNAME + ');
SET @SVR2 =(SELECT MAX([Updated]) FROM [SVR2_LINK_NAME].[dataBaseName].[dbo].['+ @TBLNAME + ']);
IF @SVR1 > @SVR2
BEGIN
SELECT ''COPY FROM SERVER FOR '+ @TBLNAME +'''
END';
EXEC (@SQLTXT);
FETCH NEXT FROM my_cursor INTO @TBLNAME;
END
CLOSE my_cursor
DEALLOCATE my_cursor
Exists Example
SELECT * FROM SHOPS AS A
WHERE EXISTS
(SELECT AREA FROM AREAS AS B
WHERE A.Country=B.Country AND
[REGION] = 'North')
In Example
SELECT * FROM SHOPS
WHERE Country IN
(SELECT Country FROM AREAS
WHERE [REGION] = 'North')
SELECT * FROM SHOPS
WHERE Country IN
('UK','France','Germany')
select [definition] from <databasename>.sys.sql_modules where object_id = object_id('<ViewName>')";
Get View Names from SELECT * from .sys.objects where type='V'
Use instead of ROWCOUNT 2012 forward [requires ORDER BY] e.g.
SELECT * FROM [database].[dbo].[table]
ORDER BY [database].[dbo].[table].[field]
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY
Selects Data From Row 50 to Row 60
..... Group By ...... With Rollup
Adds Subtotals at each grouped level (All is shown as Null in SQL SERVER)
Ideal For Reporting as standard totals/subtotals are created.
Cat 1 Cat 2 Value
=======================
A X 10
A Y 10
A NULL 20
B X 5
B Y 5
B NULL 10
NULL NULL 30
..... Group By ...... With Cube
Adds Subtotals at every possible grouped level (All is shown as Null in SQL SERVER)
Ideal For Cube type queries
Cat 1 Cat 2 Value
=======================
A X 10
A Y 10
A NULL 20
B X 5
B Y 5
B NULL 10
NULL NULL 30
NULL X 15 *
NULL Y 15 *
- Additional to Rollup
e.g. Get Latest Cost where multiple costs against a material using FIRST_VALUE
SELECT
Material,FIRST_VALUE([Cost]) AS [Lastest Cost] OVER (PARTITION BY [Material] ORDER BY [updated] Desc)
FROM
[Materials]
Uses 'Windowing' functions.
Sample Data Table='Test'
DayNo Class Value
1 a 4
1 b 5
1 c 4
2 a 7
2 b 9
2 c 4
3 a 8
3 b 2
3 c 9
USE:
SELECT
dayno,
class,
Sum(value) over(partition by class order by dayno ) as runningTotByDayNo
/* ## Not A Normal Sum QED needs group by ## */
FROM
dbTest.dbo.Test
group by
dayno,class,value
order by
dayno,class
NB: over(partition by class order by dayno )
means the sum will be reset for each class and calculated
for each dayno to give
DayNo Class runningTotByDayNo
1 a 4
1 b 5
1 c 4
2 a 11 (4+7)
2 b 14 (5+9)
2 c 8 (4+4)
3 a 19 (4+7+8)
3 b 16 (5+9+2)
3 c 17 (4+4+9)
For A Deeper analysis e.g.
Test2
DayNo Class Class2 val
1 a z 4
1 b z 5
1 c x 4
2 a z 7
2 b z 9
2 c x 4
3 a z 8
3 b z 2
3 c z 9
USE:
SELECT
dayno,
class,
class2,
Sum(value) over(partition by class,class2 order by dayno ) as runningTotByDayNo
FROM
dbTest.dbo.Test2
group by
dayno,class,class2,value
order by
dayno,class,class2
To Give
DayNo Class Class2 runningTotByDayNo
1 a z 4
1 b z 5
1 c x 4
2 a z 11 (4+7)
2 b z 14 (5+9)
2 c x 8 (4+4)
3 a z 19 (4+7+8)
3 b z 16 (5+9+2)
3 c z 9 (9)
Find DTEXEC.EXE File There may be a 64 bit and 32 bit version here forcing the 32 bit by using the full path and describing the location of file to use with the /F option
"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /F "C:\...\SomeXmlDataForTF.dtsx"
To Create The File right click on Database/Tasks/ImportData use wizard until Save and Run Package Option and Select "Save SSIS Package" AND click "File system". Name Package and finish. It should then be saved. You can edit the .dtsx package XML query to some extent.
Working Example
Content of test.csv
10,John,Football,2019-10-11,25.4,TZ,Smith,2000-09-01,Mrs Jones,£500.01,Class A,ABC 123
12,Barry,Cricket,2019-10-11,17,AF,Jones,2000-09-01,Mrs Jones,£255.97,Class B,ABC 123
Content of test.fmt.
14.0
12
1 SQLCHAR 0 10 "," 1 Age ""
2 SQLCHAR 0 10 "," 2 Name ""
3 SQLCHAR 0 10 "," 3 Fav ""
4 SQLCHAR 0 10 "," 4 End ""
5 SQLCHAR 0 10 "," 5 Val ""
6 SQLCHAR 0 10 "," 6 Key ""
7 SQLCHAR 0 10 "," 7 Name2 ""
8 SQLCHAR 0 10 "," 8 Start ""
9 SQLCHAR 0 10 "," 9 Contact ""
10 SQLCHAR 0 10 "," 10 Amount ""
11 SQLCHAR 0 10 "," 11 Class ""
12 SQLCHAR 0 10 "\r\n" 12 Code ""
NOTES
14.0 at top is version only used by BCP (Rejects too high a version though)
12 at top is field count
cols left 2 right `
1 - 12 is file field order
SQLCHAR is Host File Datatype
0 is prefix length
10 is Host Data Length
"," and "\r\n" are terminators
1 - 12 is server field order
Age Name etc. are field names for server
"" is column collation
NB ** Local files on server are ok for permissions OR using SQL Login and Permisions = Everyone on .csv and .fmt else it gets complicated... **
SQL to Use CSV File
SELECT * INTO DELETE_THIS FROM
OPENROWSET (BULK N'C:\Users\<USER>\GETSAP\csv\test.csv',
FORMATFILE ='C:\Users\<USER>\GETSAP\csv\test.fmt', CODEPAGE = '65001') as MyCSV;
OR with data conversions...
SELECT
CONVERT(int,Age) as AGE,
Name,
CONVERT(date,[END]) as [End],
CONVERT(float,Val) as Val,
[Key],
Name2,
CONVERT(date,[START]) as [START],
Contact,
Amount,
CONVERT(float,substring(Amount,2,50)) as NewAmountGBP,
Class,
Code
FROM
OPENROWSET (BULK N'C:\Users\<USER>\GETSAP\csv\test.csv',
FORMATFILE ='C:\Users\<USER>\GETSAP\csv\test.fmt', CODEPAGE = '65001') as MyCSV;
Lag brings a record back from before the current record by x records back. Requires Over (Order By Field)
SELECT
[fieldName],
LEAD([fieldName],y) Over (ORDER BY [fieldName]) AS Plus1,
LAG([fieldName],x) Over (ORDER BY [fieldName]) AS Minus1
FROM
database.dbo.tableName
See Lag, Lead brings a record back from after the current record by y records forward. Requires Over (Order By Field)
Remember you can name a transaction e.g. BEGIN TRANSACTION T1, also COMMIT when complete, use name if required e.g. COMMIT T1
BEGIN TRANSACTION
DELETE <TABLE> FROM WHERE <CRITERIA> /* Or Append Update Query Type */
ROLLBACK
Don't kill these statuses
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SIGNAL HANDLER
SP_Who2 'Active' -- To View ID's
Then
DBCC INPUTBUFFER (#) -- To View Selected Job Number
Then
Kill # -- To End Job
Example to Rollback an update if a field value of the update matches some criteria
USE [<DatabaseName>]
GO
CREATE TRIGGER
[dbo].[<TriggerName>]
ON
[dbo].[<TableName>]
AFTER
INSERT,DELETE,UPDATE
AS
BEGIN
DECLARE @<SomeVarIfRequired> int
SELECT @<SomeVarIfRequired> = [<SomeField>] FROM INSERTED;
IF @<SomeVarIfRequired> >=|=<|>|<|= <SomeValueOrVariable>
BEGIN
ROLLBACK TRANSACTION
END
END
GO
ALTER TABLE [dbo].[forecast] ENABLE TRIGGER [<TriggerName>]
GO
Basic Form for SQL Server
SELECT RowGroupField, Header1,..HeaderN
FROM
(SELECT AggregateField, ColGroupField, RowGroupField FROM Table) AS SOURCE
PIVOT (
AggregateFunction(AggregateField)
FOR
ColGroupField IN (values in ColGroupField)
) AS PIV
ORDER BY RowGroupField
Dynamic Form SQL Server
DECLARE @Headers Varchar(max);
DECLARE @OutStr Varchar(max);
DECLARE @OutComma Varchar(1);
DECLARE @Counter int;
DECLARE @Query Varchar(max);
SET @Counter = 0;
SET @OutStr = '';
SET @OutComma = '';
DECLARE prc_cursor CURSOR FOR SELECT DISTINCT <HeaderField> FROM [dbo].<TableName> WHERE <clause> ORDER BY <Header Field>
OPEN prc_cursor
FETCH NEXT FROM prc_cursor INTO @Headers
WHILE @@FETCH_STATUS=0
BEGIN
IF @Counter > 0
BEGIN
SET @OutComma = ',';
END
SET @OutStr = @OutStr + @OutComma + '[' + @Headers + ']'
SET @Counter = @Counter + 1
FETCH NEXT FROM prc_cursor INTO @Headers
END
CLOSE prc_cursor
DEALLOCATE prc_cursor
SET @Query= 'SELECT <rowField>,' + @OutStr + ' FROM
(SELECT <rowField>,<HeaderField> ,<ValueField> FROM [dbo].<table> WHERE <clause>) AS SOURCE
PIVOT (
SUM(<ValueField>)
FOR <HeaderField> IN
(' + @OutStr + ')
) AS PIV
exec(@Query)
A user only temptable = #temptable a global is ##temptable
use #TableName Drop Manually or will drop on connection ended. NB double dot .. chooses default Schema.
IF OBJECT_ID('tempDb..#someTempTable','U') IS NOT NULL
BEGIN
DROP TABLE #someTempTable
END
Right Click On Database And Select Tasks/Shrink/Files/File Type = Log
OR
USE [<dbname>]
GO
DBCC SHRINKFILE (N'<NameOfLog>' , 0, TRUNCATEONLY)
GO
Get by Right Click On Database And Select Properties/Files/Logical Name
Database Engine Tuning Advisor (DTA.exe /? for command line params) Save SQL in File and select file as workload, select database(s) to tune and [Start Analysis], Copy recomendations to clipboard OR use Actions/Apply(or Save) Recommendations
To run from SSMS right click in query pane Select Analyse query in DETA and run [Start Analysis]
Keep Indexes Per Table to arround 5 - 10 max.
/****************************************************************
SQL Texts run in the last 7 days sorted by the maximum time taken
with the database name and split into the hour in which they
were processed.
****************************************************************/
SELECT
FORMAT(last_execution_time,'HH') Hr,
name,
text,
SUM(total_worker_time) [Worker Time]
FROM
(
SELECT
last_execution_time,
total_worker_time,
name,
text
FROM
sys.dm_exec_query_stats ST
CROSS APPLY
(SELECT
*
FROM
sys.dm_exec_sql_text(ST.sql_handle) qp
INNER JOIN
sys.databases db
ON
qp.dbid = db.database_id ) TX
) RUN_DATA
WHERE
last_execution_time BETWEEN
CURRENT_TIMESTAMP-7 AND CURRENT_TIMESTAMP -- for Last 7 Days
-- OR For Specific Dates [CONVERT(datetime,'XXXX-XX-XX 00:00:00') AND CONVERT(datetime,'XXXX-XX-XX 23:59:59')]
GROUP BY
last_execution_time,name,text
ORDER BY
SUM(total_worker_time) DESC
Allows join to Functions that return Tables e.g. some of the built in functions
SELECT TOP 10
ST.last_execution_time, ST.total_worker_time, TX.dbid, TX.text
FROM
sys.dm_exec_query_stats ST /* Various Stats e.g when a query was run and the time it took */
CROSS APPLY
(SELECT * FROM sys.dm_exec_sql_text(ST.sql_handle)) TX /* Using CROSS APPLY to */
orphaned dbo fix with
USE <DatabaseName>
GO
EXEC sp_changedbowner 'newownerName|domain\NewOwner'
orphaned account fix with
USE <DatabaseName>
GO
EXEC sp_change_users_login 'Update_One', 'orphanedUser','newExistingServerUser'
create server login (windows)
USE [master]
GO
CREATE LOGIN [<domain\username>] FROM WINDOWS WITH DEFAULT_DATABASE=[<dbname>]
GO
List db users
USE <DatabaseName>
GO
SELECT * FROM sys.sysusers
List server users
SELECT * FROM master.sys.syslogins
SELECT LOGINPROPERTY('YourLoginName', 'PasswordLastSetTime');
Right click on database select Facets then Server Configuration then XPCmdShellEnabled and set to true or false.
Source https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/
-- Missing Index Script
-- Original Author: Pinal Dave
USE <dbname>
GO
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM
sys.dm_db_missing_index_groups dm_mig
INNER JOIN
sys.dm_db_missing_index_group_stats dm_migs
ON
dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN
sys.dm_db_missing_index_details dm_mid
ON
dm_mig.index_handle = dm_mid.index_handle
WHERE
dm_mid.database_ID = DB_ID()
ORDER BY
Avg_Estimated_Impact DESC
Before starting ensure the required column is a large enough varbinary()
use <databaseName>
go
INSERT INTO <someTable> SELECT '<usersUID>',ENCRYPTBYPASSPHRASE('SomeTextForAKey','UsersPassWord')
/* TO VIEW WHERE WE WROTE TO A FIELD CALLED [password] */
SELECT CONVERT(varchar, DECRYPTBYPASSPHRASE( 'SomeTextForAKey', [password] ) ) from <someTable>
Slightly ammended from SQL Authority
Steps
Set up Resource Pools with restrictions
Set up workgroups that point to the Pools
Create a function to allocate work based on App Name, User etc
Alter the Govenor to use the function
Reconfigure the Govenor
-----------------------------------------------
-- Sample items that csan be used in classification function
-- SELECT SUSER_NAME(),USER_NAME(),SCHEMA_ID(),
-- NT_CLIENT(),HOST_NAME(),APP_NAME(),
-- DATEPART(HOUR,SYSDATETIME())
-----------------------------------------------
-----------------------------------------------
-- Step 1: Create Resource Pool
-----------------------------------------------
-- Creating Resource Pool1
CREATE RESOURCE POOL Pool1
WITH
( MIN_CPU_PERCENT = 0,
MAX_CPU_PERCENT = 30,
MIN_MEMORY_PERCENT = 0,
MAX_MEMORY_PERCENT = 30
-- additional options
---------------------
-- AFFINITY SCHEDULER = (0 TO 63, 128 TO 191),
-- MIN_IOPS_PER_VOLUME = value
-- MAX_IOPS_PER_VOLUME = value
)
GO
-- Creating Resource Pool2
CREATE RESOURCE POOL Pool2
WITH
( MIN_CPU_PERCENT = 50,
MAX_CPU_PERCENT = 100,
MIN_MEMORY_PERCENT = 50,
MAX_MEMORY_PERCENT = 100
-- additional options
---------------------
-- AFFINITY SCHEDULER = (0 TO 63, 128 TO 191),
-- MIN_IOPS_PER_VOLUME = value
-- MAX_IOPS_PER_VOLUME = value
)
GO
-----------------------------------------------
-- Step 2: Create Workload Group
-----------------------------------------------
-- Creating Workload Group for Pool1
CREATE WORKLOAD GROUP WGroup1
USING Pool1 ;
GO
-- Creating Workload Group for Pool2
CREATE WORKLOAD GROUP WGroup2
USING Pool2 ;
GO
--------------------------------------------------
-- Step 3: Create Function to Route Workload Group
--------------------------------------------------
CREATE FUNCTION dbo.Classifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF ( SUSER_NAME() = 'User1' )
SET @WorkloadGroup = 'WGroup1'
ELSE IF ( SUSER_NAME() = 'User2' )
SET @WorkloadGroup = 'WGroup2'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO
-----------------------------------------------
-- Step 4: Enable Resource Governer
-- with Function Classifier
-----------------------------------------------
ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = dbo.Classifier );
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
-----------------------------------------------
-- Step 5: Clean Up
-- Run to clean up test
-----------------------------------------------
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION dbo.Classifier
GO
DROP WORKLOAD GROUP WGroup1
GO
DROP WORKLOAD GROUP WGroup2
GO
DROP RESOURCE POOL Pool1
GO
DROP RESOURCE POOL Pool2
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
Add server NB change rmtuser/rmtpassword
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\user',@rmtpassword='password'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Read Groups NB 900 Record Limit and link needs setting as above first change rmtuser/rmtpassword
select TOP 900 *
from OpenQuery(ADSI, '
SELECT samaccountname,mail,sn,name, cn, objectCategory
FROM ''LDAP://DOMAIN.com''
WHERE objectCategory=''group''
ORDER BY cn
')
Read Users
SELECT TOP 900 *
FROM OpenQuery (
ADSI,
'SELECT c,cn,displayName,givenname,homedrive,
initials,name,objectCategory,profilePath,sAMAccountNAme,SN,title,
userAccountControl,userPrincipalName,company,department,location,
manager,pwdLastSet,dnsHostName
FROM
''LDAP://DOMAIN''
WHERE
objectClass =''User''
ORDER BY
displayName'
)
- Current Session
- Current Users
- Log File Shrink
- Logins see Orphaned Accounts
- Kill
- Resource Governor
- Show Running Queries
- sp_who2 filter
- Waiting Requests
- Waiting Tasks
sp_who2
select * from sys.dm_exec_sessions order by status
select * from sys.dm_exec_requests WHERE sql_handle is not null
select * from sys.dm_os_waiting_tasks
Run then see who is running with sp_who2
SELECT
txt.[text],*
FROM
sys.dm_exec_requests AS rq
CROSS APPLY
sys.dm_exec_sql_text(rq.sql_handle) AS txt
exec sp_who2
OR
/* SHOW CURRENT RUNNING QUERIES */
DECLARE @handle varbinary(1000)
DECLARE @str_handle varchar(max)
DECLARE @SQL varchar(max)
DECLARE qCursor CURSOR FOR select sql_handle from sys.dm_exec_requests WHERE sql_handle is not null
OPEN qCursor
FETCH NEXT FROM qCursor INTO @handle
WHILE @@FETCH_STATUS=0
BEGIN
set @str_handle = sys.fn_sqlvarbasetostr(@handle)
SET @SQL = 'select * from sys.dm_exec_sql_text(' + @str_handle + ')'
print @SQL
exec(@SQL)
FETCH NEXT FROM qCursor INTO @handle
END
CLOSE qCursor
DEALLOCATE qCursor
From sqlmatters.com
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT *
FROM #sp_who2
-- Add any filtering of the results here :
WHERE DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY DBName ASC
DROP TABLE #sp_who2
Add Auto increment CONSTRAINT PK_
PRIMARY KEY CLUSTERED Adds the Index Can be added later if requiredALTER TABLE dbo.<Table>
ADD ID INT IDENTITY
CONSTRAINT PK_<Table> PRIMARY KEY CLUSTERED
USE [<YOUR_DB>]
GO
EXEC sp_rename [<OLD_TABLE_NAME>],[<NEW_TABLE_NAME>]
look for dbi_dbccLastKnownGood for corruption info showing last good DBCC CHECKDB (<DB_NAME>)
/*SQL SERVER 2012 SET UP FOR EXCEL DRIVER */
/******************************************/
EXEC sys.sp_enum_oledb_providers
SP_CONFIGURE 'show advanced options', 1;
GO
RECONFIGURE;
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1 -- DynamicParameters for 2014
/* TEST WITH -- ENSURE ACCESS OF SQL ACC TO C:\.... */
/****************************************************/
SELECT XLqry.* FROM
OPENROWSET
( 'Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0 XML;Database=C:\....\SomeSpreadsheet.xlsx;',
'SELECT * FROM [SomeSheetName$]'
) AS XLqry
Show only records from table1 not also in table2
SELECT * FROM table1
EXCEPT
SELECT * FROM table2
For Complete info also run
SELECT * FROM table2
EXCEPT
SELECT * FROM table1
e.g. JOB list
SELECT * FROM msdb.dbo.sysjobs
and Useful JOB info excluding POWER BI Jobs
SELECT name, description, last_run_date, command FROM msdb.dbo.sysjobs jb
INNER JOIN msdb.dbo.sysjobsteps js
ON
jb.job_id = js.job_id
where
jb.description <> 'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'
AND subsystem='TSQL'
e.g. LIST ALL SQL SAVED IN DB
SELECT * FROM sys.all_sql_modules
e.g. LIST ALL COLUMNS IN DB
SELECT * FROM sys.all_columns
e.g. LIST ALL OBJECTS IN DB
SELECT * FROM sys.all_objects
See
sys.all_views
sys.tables
sys.procedures
ALTER TABLE [DB].[dbo].[Table]
ADD CONSTRAINT [FieldName] CHECK ([FieldName] in ('Value_01','Value_02','Value_03'))
goto query
--NB add < "APP"=>"<SOME_APP_NAME>" > to sqlserver connection array in php for detail trace info
--Set Trace Vars
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
--Set Input Vars
set @DateTime = '2023-03-02 08:42:00.000' -- ### END TIME ####
set @maxfilesize = 5 -- MB
--Create trace and capture ID @TraceID and result @rc
--NB File must be writeable by service account and not already exist e.g. ( "C:\temp\Trace" )
--May need to override permissions on saved file to access
exec @rc = sp_trace_create @TraceID output, 0, N'C:\temp\Trace', @maxfilesize, @Datetime
if (@rc != 0) goto error
-- Set the events to capture ( see https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setevent-transact-sql?view=sql-server-ver15)
declare @on bit
set @on = 1 -- use to turn on event
exec sp_trace_setevent @TraceID, 13, 1, @on -- 13 Batch Starting, 1 = Text
exec sp_trace_setevent @TraceID, 13, 6, @on -- 13 Batch Starting, 6 = NTUserName
exec sp_trace_setevent @TraceID, 13, 7, @on -- 13 Batch Starting, 7 = NTDomainName
exec sp_trace_setevent @TraceID, 13,10, @on -- 13 Batch Starting,10 = ApplicationName
exec sp_trace_setevent @TraceID, 13,11, @on -- 13 Batch Starting,11 = LoginName
exec sp_trace_setevent @TraceID, 13,11, @on -- 14 Batch Starting,11 = Start
exec sp_trace_setevent @TraceID, 13,11, @on -- 15 Batch Starting,11 = End
exec sp_trace_setevent @TraceID, 13,60, @on -- 15 Batch Starting,60 = Is System
-- Add Filter example exclude Login matching *NT AUTH*
--exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%NT AUTH%' -- Where 11 = LoginName ,0 = "=", 7 = "NOT LIKE" i.e LoginName = NOT LIKE N'%NT AUTH%'
-- filter for text in sql
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%<SOME_TEXT>%' -- Where 1 = Text ,0 = "=", 6 = "LIKE"
-- Set the trace status to 1 to start the trace
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
SELECT * FROM master.sys.traces
Get the trace details from master.sys.traces
View filtered trace file
SELECT * FROM ::fn_trace_gettable('C:\<LOCATION_OF_TRC_FILE>.trc', default) where NTUserName = '<SOME_TEXT>'
or
use <DATABASE_NAME>
go
SELECT * INTO <TABLE_NAME> FROM ::fn_trace_gettable('C:\<LOCATION_OF_TRC_FILE>.trc', default)
SELECT * FROM <TABLE_NAME> where NTUserName <> '<SOME_TEXT>' -- ## example to remove one user ##
DROP TABLE <TABLE_NAME>
To Stop Trace
-- To Stop a trace
EXEC sp_trace_setstatus @traceid = id as int , @status = 0;
-- Closes and delete
EXEC sp_trace_setstatus @traceid = id as int , @status = 2;
Create A trace (can only be to table via profiler) - the file can be opened in profiler and sent to a table later if required
--Set Trace Vars
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
--Set Input Vars
set @DateTime = '2021-07-21 11:55:00.000' -- END TIME EXAMPLE
set @maxfilesize = 5 -- MB
--Create trace and capture ID @TraceID and result @rc
--NB File must be writeable by service account and not already exist e.g. ( "C:\temp\trace.trc" here )
--May need to override permissions on saved file to access
exec @rc = sp_trace_create @TraceID output, 0, N'C:\temp\trace.trc', @maxfilesize, @Datetime
if (@rc != 0) goto error
-- Set the events to capture ( see https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setevent-transact-sql?view=sql-server-ver15)
declare @on bit
set @on = 1 -- use to turn on event
exec sp_trace_setevent @TraceID, 17, 1, @on -- Where 17 = event = ExistingConnection before the trace started. -- 1 = Value to return here Text
exec sp_trace_setevent @TraceID, 17, 9, @on -- 17 as above, 9 = ClientProcessID
-- Add Filter example exclude Login matching *NT AUTH*
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%NT AUTH%' -- Where 11 = LoginName ,0 = "=", 7 = "NOT LIKE" i.e LoginName = NOT LIKE N'%NT AUTH%'
-- Set the trace status to 1 to start the trace
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
source https://dataedo.com/kb/query/sql-server/find-the-time-when-table-was-last-accessed
USE <ENTER_DB_NAME>
go
select [schema_name],
table_name,
max(last_access) as last_access
from(
select schema_name(schema_id) as schema_name,
name as table_name,
(select max(last_access)
from (values(last_user_seek),
(last_user_scan),
(last_user_lookup),
(last_user_update)
) as tmp(last_access))
as last_access
from sys.dm_db_index_usage_stats sta
join sys.objects obj
on obj.object_id = sta.object_id
and obj.type = 'U'
and sta.database_id = DB_ID()
) usage
group by schema_name,
table_name
order by last_access desc;
e.g. grant permission to view definitions of views on a schema with.
USE <SOME_DB>
GO
GRANT VIEW DEFINITION ON SCHEMA::DBO TO [<USER_NAME>]
-- REMOVE WITH
REVOKE VIEW DEFINITION ON SCHEMA::DBO TO [<USER_NAME>]
see MS Docs for details
NB Create Table requires Alter on schema e.g.
USE [databasename]
GRANT ALTER ON Schema :: [schemaname] TO [username]
GRANT CREATE TABLE TO [username]
GO
view permissions of a user
USE <DB_NAME>
GO
--For Windows User May be able to use...
SELECT principal_id FROM sys.login_token where name = '<DOMAIN>\<USERNAME>'
--For SQL User
SELECT principal_id FROM sys.database_principals where name = '<SQL_USERNAME>'
SELECT
name,object_id,type_desc,O.[type]
FROM
sys.objects O,
sys.database_permissions P
WHERE
P.grantee_principal_id = <VALUE OF principal_id FROM ABOVE> AND
O.object_id = P.major_id
/* FOR SINGLE USER - CAN TAKE A WHILE */
ALTER DATABASE GEN SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* TO RESTORE */
ALTER DATABASE GEN SET MULTI_USER
For Tables
USE [<SOME_DB>]
GO
/* ADD PROPERTY */
EXEC sys.sp_addextendedproperty @name=N'<YOUR_PROPERTY_NAME>', @value=N'<YOUR_DESCRIPTION>' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'<YOUR_TABLE_NAME>'
SELECT * FROM [<SOME_DB>].[sys].extended_properties
/* UPDATE PROPERTY */
EXEC sp_updateextendedproperty @name=N'<YOUR_PROPERTY_NAME>', @value=N'<YOUR_DESCRIPTION>' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'<YOUR_TABLE_NAME>'
SELECT * FROM [<SOME_DB>].[sys].extended_properties
/* REMOVE PROPERTY */
EXEC sp_dropextendedproperty @name=N'<YOUR_PROPERTY_NAME>', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'<YOUR_TABLE_NAME>'
SELECT * FROM [<SOME_DB>].[sys].extended_properties
You can use LIKE as follows
WHERE [X] LIKE '[0-9][0-9]' -- 2 Numbers (cant use {2})
WHERE [X] LIKE '[0-9][0-9]%' -- Starts with 2 Numbers
WHERE [X] LIKE '[A][B]%' -- Starts with AB
WHERE [X] LIKE '[AB]%' -- Starts with A OR B
WHERE [X] LIKE '[C-F]%' -- Starts with C,D,E OR F
WHERE [X] LIKE '[AB]%[E]' -- Starts with A OR B Ends with E
Create Subqueries outside main query using WITH SubQ_01_Name AS (SOME SQL),SubQ_02_Name AS (SOME SQL) see example below. Can be nested.
USE <Your_Database_Name>
GO
WITH
QA AS -- E.G.
( SELECT <FIELDS_AND_EXPRESIONS> FROM <SOME_TABLE> WHERE <SOME_CLAUSE> GROUP BY <FIELDS> ) ,
QB AS
( SELECT <DIFFERENT_FIELDS_AND_EXPRESIONS> FROM <SOME_OTHER_TABLE> WHERE <SOME_OTHER_CLAUSE> GROUP BY <OTHER_FIELDS> ),
QBB AS
( SELECT <FIELDS_AND_EXPRESIONS_RE_QB> FROM [QB] <ADITTIONAL_CLAUSES_GROUPING_ETC> )
SELECT
*
FROM
QA
JOIN
QBB
ON
QA.[<FIELD>]=QBB.[<FIELD>]
GROUP BY
<FIELD_LIST>
ORDER BY
<FIELD_LIST>
-- CREATE USER LOGIN
--------------------
USE [master]
CREATE LOGIN [<USER_ID>] WITH PASSWORD=N'<INITIAL_PASSWORD>', DEFAULT_DATABASE=[<DATABASE_1>], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
-- CREATE DATABASE_1 USER FOR <USER_ID>
---------------------------------------
USE [<DATABASE_1>]
CREATE USER [<USER_ID>] FOR LOGIN [<USER_ID>]
-- ADD <DATABASE_2> ROLES
-------------------------
ALTER ROLE [db_datareader] ADD MEMBER [<USER_ID>]
ALTER ROLE [db_datawriter] ADD MEMBER [<USER_ID>]
-- CREATE DATABASE_2 USER FOR <USER_ID>
----------------------------------------
USE [<DATABASE_2>]
CREATE USER [<USER_ID>] FOR LOGIN [<USER_ID>]
-- ADD <DATABASE_2> ROLES
-------------------------
ALTER ROLE [db_datareader] ADD MEMBER [<USER_ID>]
ALTER ROLE [db_datawriter] ADD MEMBER [<USER_ID>]
-- CHANGE PASSWORD
------------------
USE [master]
GO
ALTER LOGIN [<USER_ID>] WITH PASSWORD=N'<NEW_PASSWORD>'
GO
-- DROP USER
------------
USE [<DATABASE_2>]
DROP USER [<USER_ID>]
USE [<DATABASE_1>]
DROP USER [<USER_ID>]
USE [master]
DROP LOGIN [<USER_ID>]
See Active Directory for other examples
For Linked Server
- Use OraOLEDB.Oracle and check "Allow inprocess" in Providers Section. When Adding
- Name Linked Server in dialog
- Select Provider ORACLE
- Data Source = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<SERVER_ADDRESS>)(PORT=<YOUR_PORT_NO>)))(CONNECT_DATA=(SERVICE_NAME=<YOUR_SERVICE_NAME>))) Security Tab Add Remote Login and Password (4th option)
Example for top 10 rows
SELECT
*
FROM
OPENQUERY( [<LINKED_SERVER_NAME>],
'SELECT * FROM (SELECT * FROM <TABLE_NAME>) WHERE ROWNUM<10'
)
/* CREATE SCHEMA FOR USER TO FULLY OWN WITHOUT ACCESS TO OTHE DATA */
USE <SOME_DATABASE>;
CREATE ROLE <SOME_ROLE_NAME> AUTHORIZATION dbo;
EXEC sp_addrolemember <SOME_ROLE_NAME>, <SOME_USER>;
GO
CREATE SCHEMA <SOME_SCHEMA_NAME> AUTHORIZATION <SOME_ROLE_NAME>;
GO
GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
UPDATE, VIEW DEFINITION ON SCHEMA::<SOME_SCHEMA_NAME> TO <SOME_ROLE_NAME>;
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO <SOME_ROLE_NAME>;
--2016 Version
--SELECT DB
--------------
USE <DATABASE>
GO
--TO ADD SCHEMA
----------------
CREATE SCHEMA <SCHEMA>
GO
CREATE ROLE <ROLE> AUTHORIZATION dbo
GO
GRANT CREATE TABLE TO <ROLE>
GRANT CREATE VIEW TO <ROLE>
GO
ALTER ROLE <ROLE> ADD MEMBER [<MEMBER>] -- Must Be in Members DB Users
GO
ALTER AUTHORIZATION ON SCHEMA::<SCHEMA> TO <ROLE>
GO
-- TO REMOVE SCHEMA
-------------------
-- NB SOME OWNED OBJECTS MAY NEED PRIOR REMOVAL
ALTER ROLE [<ROLE>] DROP MEMBER [<MEMBER>]
GO
ALTER AUTHORIZATION ON SCHEMA::[<SCHEMA>] TO dbo
GO
DROP ROLE [<ROLE>]
GO
DROP SCHEMA <SCHEMA>
GO
SQL Server Express Requires:
- TCP and Pipes on in sql config.
- Firewall on server to allow tcp on private network for port 1433
- Connection to be made as ,1433<instance_name>
Run the below on old server to create sp_help_revlogin
Run "EXEC sp_help_revlogin" on old server ( Set Output to text ) NB SHA is wrong (1 Not 256) on pre 2012 servers
Use output text as script on new server ( NB Remove non-required users )
USE [master]
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE [dbo].[sp_hexadecimal]
(
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
)
AS
BEGIN
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
END
go
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin]
(
@login_name sysname = NULL
)
AS
BEGIN
DECLARE @name SYSNAME
DECLARE @type VARCHAR (1)
DECLARE @hasaccess INT
DECLARE @denylogin INT
DECLARE @is_disabled INT
DECLARE @PWD_varbinary VARBINARY (256)
DECLARE @PWD_string VARCHAR (514)
DECLARE @SID_varbinary VARBINARY (85)
DECLARE @SID_string VARCHAR (514)
DECLARE @tmpstr VARCHAR (1024)
DECLARE @is_policy_checked VARCHAR (3)
DECLARE @is_expiration_checked VARCHAR (3)
Declare @Prefix VARCHAR(255)
DECLARE @defaultdb SYSNAME
DECLARE @defaultlanguage SYSNAME
DECLARE @tmpstrRole VARCHAR (1024)
IF (@login_name IS NULL)
BEGIN
DECLARE login_curs CURSOR
FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name
FROM sys.server_principals p
LEFT JOIN sys.syslogins l ON ( l.name = p.name )
WHERE p.type IN ( 'S', 'G', 'U' )
AND p.name <> 'sa'
ORDER BY p.name
END
ELSE
DECLARE login_curs CURSOR
FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name
FROM sys.server_principals p
LEFT JOIN sys.syslogins l ON ( l.name = p.name )
WHERE p.type IN ( 'S', 'G', 'U' )
AND p.name = @login_name
ORDER BY p.name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')
BEGIN'
Print @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE
BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM sys.sql_logins
WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM sys.sql_logins
WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = '
+ @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
SET @Prefix = '
EXEC master.dbo.sp_addsrvrolemember @loginame='''
SET @tmpstrRole=''
SELECT @tmpstrRole = @tmpstrRole
+ CASE WHEN sysadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin''' ELSE '' END
+ CASE WHEN securityadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin''' ELSE '' END
+ CASE WHEN serveradmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin''' ELSE '' END
+ CASE WHEN setupadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin''' ELSE '' END
+ CASE WHEN processadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin''' ELSE '' END
+ CASE WHEN diskadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin''' ELSE '' END
+ CASE WHEN dbcreator = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator''' ELSE '' END
+ CASE WHEN bulkadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin''' ELSE '' END
FROM (
SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName],
sysadmin,
securityadmin,
serveradmin,
setupadmin,
processadmin,
diskadmin,
dbcreator,
bulkadmin
FROM sys.syslogins
WHERE ( sysadmin<>0
OR securityadmin<>0
OR serveradmin<>0
OR setupadmin <>0
OR processadmin <>0
OR diskadmin<>0
OR dbcreator<>0
OR bulkadmin<>0
)
AND name=@name
) L
PRINT @tmpstr
PRINT @tmpstrRole
PRINT 'END'
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
END
Use SET NOEXEC ON to compile and test a query without running
SET NOEXEC ON
<SQL SCRIPT TO TEST>
SET NOEXEC OFF
Use SQL Command Mode in SSMS allows the use of
:setvar VAR_NAME "<VAR_VALUE>"
-- use as
SELECT * FROM $(VAR_NAME)
Commands
GO [count] --
:List -- Prints the content of the statement cache.
[:] RESET -- Clears the statement cache.
:Error -- Redirect all error output to the file specified by
[:] ED -- Starts the text editor.
:Out --
[:] !! -- < command>
Executes operating system commands. To execute an operating system command, start a line with two exclamation marks (!!) followed by the operating system command. For example:
:!! Dir
:Perftrace --
[:] QUIT -- Causes sqlcmd to exit.
:Connect --
[:] EXIT (statement)--
:EXIT
Does not execute the batch, and then quits immediately and returns no value.
:EXIT( )
Executes the batch, and then quits and returns no value.
:EXIT(query)
Executes the batch that includes the query, and then quits after it returns the results of the query.
:On Error --
:r -- run from a file
:Help --
:ServerList -- Lists the locally configured servers
:XML [ON | OFF] --
:Setvar -- see above
:Listvar -- Displays a list of the scripting variables that are currently set.
--RENAME DB AND TABLES
----------------------
ALTER DATABASE <OLD_DB_NAME> MODIFY NAME = <NEW_DB_NAME>
GO
USE <NEW_DB_NAME>;
GO
EXEC sp_rename '<OLD_TABLE_NAME>' , '<NEW_TABLE_NAME>';
EXEC sp_rename '<OLD_TABLE_NAME>' , '<NEW_TABLE_NAME>';
DECLARE @DB VARCHAR(MAX);
SET @DB = '<SOMEDB>';
EXEC('USE ' + @DB +';
SELECT
Ob.name as [Object],
Ob.type_desc as [Object Type],
Ref.name AS [Linked To],
Ref.type_desc AS [Linked To Type]
FROM
sys.sql_dependencies D
LEFT JOIN
sys.all_objects Ob
ON
D.object_id = Ob.object_id
LEFT JOIN
sys.all_objects Ref
ON
D.referenced_major_id = Ref.object_id
GROUP BY
Ob.name,
Ob.type_desc,
Ref.name,
Ref.type_desc
SELECT
Ob.name as [Object],
Ob.type_desc as [Object Type],
Ref.name AS [Linked To],
Ref.type_desc AS [Linked To Type]
FROM
sys.sql_expression_dependencies D
LEFT JOIN
sys.all_objects Ob
ON
D.referencing_id = Ob.object_id
LEFT JOIN
sys.all_objects Ref
ON
D.referenced_id = Ref.object_id
GROUP BY
Ob.name,
Ob.type_desc,
Ref.name,
Ref.type_desc')
Force Col1 and 2 of Table1 to be Latin1_General_CI_AS
SELECT
Col1 COLLATE Latin1_General_CI_AS AS Col1,
Col2 COLLATE Latin1_General_CI_AS AS Col2
FROM
Table1
UNION ALL
SELECT
Col1,
Col2
FROM
Table2
Regularly clear backup history say to 90 days
USE msdb;
GO
EXEC sp_delete_backuphistory @oldest_date = '2010-01-01';
create database test
go
use test
go
create table A (
fldA int
)
create table B (
fldA int
)
create table C (
fldA int
)
insert into A (fldA) VALUES (1)
insert into A (fldA) VALUES (2)
insert into A (fldA) VALUES (3)
insert into B (fldA) VALUES (4)
insert into B (fldA) VALUES (5)
insert into B (fldA) VALUES (6)
insert into C (fldA) VALUES (7)
insert into C (fldA) VALUES (8)
insert into C (fldA) VALUES (9)
CREATE ROLE [AB]
GRANT SELECT ON [dbo].[A] TO [AB]
GRANT SELECT ON [dbo].[B] TO [AB]
CREATE USER [<SOME_SQL_USER>] FOR LOGIN [<SOME_SQL_USER>]
ALTER ROLE [AB] ADD MEMBER [<SOME_SQL_USER>]
EXECUTE AS LOGIN='<SOME_SQL_USER>'
select * from A
select * from B
BEGIN TRY
select * from C
END TRY
BEGIN CATCH
select 'cant select from C' as [error 4 user with role]
END CATCH
REVERT --BACK to std user
drop table A
drop table B
drop table C
use master
go
drop database test
go
MERGE target_table t USING source_table s
ON merge_condition -- e.g. t.field = s.field AND t.field2 = s.field2 etc...
WHEN MATCHED
THEN update_statement -- e.g. THEN SET t.f1 = s.f1, t.f2 = s.f2 etc.
WHEN NOT MATCHED
THEN insert_statement -- e.g. THEN INSERT (f1, f2, f3) VALUES (s.f1, s.f2, s.f3)
WHEN NOT MATCHED BY SOURCE
THEN DELETE; -- e.g. if not matched not required
DECLARE @dbname NVARCHAR(MAX)
DECLARE @statement NVARCHAR(MAX)
DECLARE @userList NVARCHAR(MAX)
SET @userList = '''<USER1>'',''<USER2>'',''<USER3>'',''<USER4>'''
DECLARE db_cursor CURSOR
FOR
SELECT name FROM master.sys.databases WHERE name NOT IN ('master','msdb','model','tempdb') AND state_desc='online'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';' + '
SELECT
ServerName=@@servername,
dbname=db_name(db_id()),
p.name as UserName,
p.type_desc as TypeOfLogin,
pp.name as PermissionLevel,
pp.type_desc as TypeOfRole
FROM
sys.database_role_members roles
JOIN
sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN
sys.database_principals pp ON roles.role_principal_id = pp.principal_id
WHERE
p.name IN ('+ @userList +')'
EXEC sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
/* SYSADMINS */
/*************/
SELECT
@@SERVERNAME as SQLInstance,
R.name AS server_role,
P.name AS role_member
FROM
sys.server_role_members RM
INNER JOIN
sys.server_principals P
ON
RM.member_principal_id = P.principal_id
INNER JOIN
(SELECT principal_id, name FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE') R
ON
RM.role_principal_id = R.principal_id
CREATE EVENT SESSION [SQL_Text_Trace] ON SERVER
-- Replace <@@@YOUR_TEXT@@> with sql text to filter for
ADD EVENT sqlserver.rpc_completed(
WHERE ( [sqlserver].[like_i_sql_unicode_string]
([sqlserver].[sql_text],N'%<@@@YOUR_TEXT@@@>%')
)
),
ADD EVENT sqlserver.rpc_starting(
WHERE ( [sqlserver].[like_i_sql_unicode_string]
( [sqlserver].[sql_text],N'%<@@@YOUR_TEXT@@@>%')
)
),
ADD EVENT sqlserver.sp_statement_starting(
WHERE ([sqlserver].[like_i_sql_unicode_string]
([sqlserver].[sql_text],N'%<@@@YOUR_TEXT@@@>%')
)
),
ADD EVENT sqlserver.sql_batch_starting(
SET collect_batch_text=(1)
ACTION(
package0.event_sequence,
sqlserver.client_app_name,
sqlserver.client_connection_id,
sqlserver.client_hostname,
sqlserver.nt_username,
sqlserver.session_id,
sqlserver.sql_text,
sqlserver.username
)
WHERE ([sqlserver].[like_i_sql_unicode_string]
( [sqlserver].[sql_text],N'%<@@@YOUR_TEXT@@@>%')
)
),
ADD EVENT sqlserver.sql_statement_starting(
WHERE ([sqlserver].[like_i_sql_unicode_string]
([sqlserver].[sql_text],N'%<@@@YOUR_TEXT@@@>%')
)
)
WITH (
MAX_MEMORY=8192 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,
STARTUP_STATE=OFF
)
GO
NB * allows multi files to be joined in one query
select
top 10 *
from
sys.fn_xe_file_target_read_file('C:\<path>\<filename>*.xel', null, null, null)
select
top 10 cast(event_data as XML) as event_data
from
sys.fn_xe_file_target_read_file('C:\<path>\<filename>*.xel', null, null, null)
With this input "cross apply XML.event_data.nodes('event') as tbl(fld)" creates a table of fields that can be accessed and used with the XML query 'action[@name="client_app_name"]/value)[1]' to find <action name="client_app_name" sub to the event node.
select
fld.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') as [client_app_name]
from
(
select top 10 cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('C:\<path>\<filename>*.xel', null, null, null)
) XML
cross apply XML.event_data.nodes('event') as tbl(fld)
tbl can be anything not used again and fld can be anthing but used in top level select statement.
SELECT TOP 10 * FROM (
select
n.value('(@timestamp)[1]', 'datetime2') as [utc_timestamp],
n.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') as [client_app_name],
n.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') as [client_hostname],
n.value('(action[@name="username"]/value)[1]', 'nvarchar(max)') as [username],
n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(max)') as [database_name],
n.value('(action[@name="is_system"]/value)[1]', 'nvarchar(5)') as [is_system],
n.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as [sql_text]
from (select
cast(event_data as XML) as event_data
from
sys.fn_xe_file_target_read_file('C:\<path>\<filename>*.xel', null, null, null)
)ed
cross apply ed.event_data.nodes('event') as tq(n)
) XML
WHERE
[sql_text] not like '%'C:\<path>\<filename>*.xel%' AND
(
[sql_text] like '%<SOME TEXT>%'
)
e.g. the below would create suitable for the above
CREATE EVENT SESSION [audit] ON SERVER
ADD EVENT sqlserver.degree_of_parallelism( ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,
sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'C:\<PATH>.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0
KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
-- SET OPTIONS TO USE OLE
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
-- CREATE VARS
DECLARE
@init int
, @data varbinary(max)
, @fPath varchar(max)
-- SET FILE PATH E.G. IF ITS AN xlsm FILE
SET @fPath = 'c:\temp\temp.xlsm'
-- GET BINARY DATA TO VAR
SET @data = (SELECT [<VARBIN_FIELD>] FROM [<TABLE>] WHERE [<SOME_FIELD>] = '<SOME_CLAUSE>')
-- WRITE TO FILE
EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instance created
EXEC sp_OASetProperty @init, 'Type', 1;
EXEC sp_OAMethod @init, 'Open'; -- Calling a method
EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources
-- RESET OPTIONS FOR OLE
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO
use sqlcmd -y 0 for complete fields ( beware! )
example outputs a non truncated sql definition to temp.txt
sqlcmd -S <SERVER> -E -d <DATABASE> -Q "select CAST(definition as varchar(max)) from sys.all_sql_modules where object_id = <SOME_ID>" -o temp.txt -y 0
DBCC CHECKIDENT ('[<TableName>]', RESEED, 0);
GO
Rolls back all transactions as errors as 1/0
Example named transaction T001 but can be anything:
BEGIN TRY
BEGIN TRANSACTION T001
INSERT INTO [<TABLE>] ([<FIELD>]) VALUES (1)
INSERT INTO [<TABLE>] ([<FIELD>]) VALUES (1/0)
COMMIT TRANSACTION T001
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T001
SELECT 'ERROR ROLLBACK'
END CATCH
/* SET UP */
CREATE TABLE AON_TEST (
TestVal float
)
/* SET UP END */
DECLARE @TEST Varchar(max)
DECLARE my_cursor CURSOR FOR (
SELECT 1 AS CNT UNION
SELECT 0 -- change between 0 and 2 to see the effect
)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @TEST
BEGIN TRANSACTION AON -- All Or Nothng!
BEGIN TRY
/* Everyting in here */
INSERT INTO AON_TEST (TestVal) VALUES ( 99 );
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO AON_TEST (TestVal) VALUES ( 1/@TEST);
FETCH NEXT FROM my_cursor INTO @TEST
END
/* End Everyting in here */
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION AON
Print 'Failed All Rolled Back'
END CATCH
IF @@trancount > 0
BEGIN
COMMIT TRANSACTION
Print 'All OK'
END
CLOSE my_cursor
DEALLOCATE my_cursor
/* DISPLAY RESULT AND REM SET UP */
SELECT * FROM AON_TEST
DROP TABLE AON_TEST
/* REM SET UP END */
exec sp_executesql N'SELECT * FROM [dbo].[<TABLE>] WHERE [<FIELD_1>]=@P1 AND [<FIELD_1>]=@P2',N'@P1 nvarchar(max),@P2 nvarchar(max)',@P1='<SOME_TEXT>',@P2='<SOME_TEXT>'
If the xml includes NAMESPACES use WITH XMLNAMESPACES as below
If the namespace has no Id delare a name then use ite each time
If the namespace has an Id use the name given
If the xml is text cast to xml as in the example
First use .query and trim the query until only one value exists then use the same query with value (Note value requires field type)
Note () before[1] -- first value
Refer to as /namespacenameIfAny:tagname/namespacenameIfAny:tagname if no namespace /tagname/tagname
Add attribute name as /namespacenameIfAny:UserConfigurableDateTime[@name="attributeName "]
To use namespaces start with
WITH XMLNAMESPACES ( 'http://www.etc' as nameinXmlDoc|Or|makeUpAName , 'http://www.etc2' AS nameinXmlDoc)
-- use nameinXMLDoc where in the Doc and makeUpAName elsewhere as /nameinXmlDoc:tag/... and /makeUpAName:tag/...
Select
-- use query first
try_cast(xml as xml).query('/rootTag/nextTag') -- might be /someName:rootTag/someName:nextTag/
-- use value when happy
try_cast(xml as xml).value('(/rootTag/nextTag)[1]','varchar(max)') -- above returns xml this returns a value
From <someTableWithATextFieldCalledXmlConatiningXmlString>
--NB use cross apply to break an Xml string with multi records into records