= tsql =
Microsoft Transact SQL (t-sql)

== Show stored procedure or function  code ==
{{{#!highlight sql
exec sp_helptext 'schema.storedProc'
exec sp_helptext 'schema.function'
}}}

== Show CLR assemblies ==
{{{#!highlight sql
-- get assembly_id
select * from sys.assembly_modules
}}}

== Extract SQL CLR assembly ==
[[http://serverfault.com/questions/139703/extracting-a-sqlclr-assembly]]

{{{#!highlight sql
-- reconfigure
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

-- extract assembly
DECLARE @IMG_PATH VARBINARY(MAX)
DECLARE @ObjectToken INT

SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65824 and file_id=1

EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'c:\windows\temp\65824.dll', 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken
}}}

== Number seconds between two dates ==
{{{#!highlight sql
SELECT datediff(second, dateX1, GETDATE() )
FROM tablex
}}}

== Convert dates to string ==
Convert types:
http://msdn.microsoft.com/en-us/library/ms187928%28SQL.90%29.aspx

{{{#!highlight sql
SELECT CONVERT(NVARCHAR(30), GETDATE(), 126)
2009-05-01T14:18:12.430

SELECT CONVERT(NVARCHAR(30), GETDATE(), 120)
2009-05-01 14:18:12
}}}

== Create stored procedure ==
{{{#!highlight sql
IF OBJECT_ID('insertName', 'P') IS NOT NULL 
BEGIN
    DROP PROC insertName;
END
GO

CREATE PROCEDURE insertName 
    @name as nvarchar(1024) , @commited as int output 
AS
BEGIN
    declare @newIdTbl1 as int;
    set @commited=0;
    begin try  
        begin transaction
        insert into tbl1(namex) values(@name);
        SELECT @newIdTbl1 = SCOPE_IDENTITY();
        insert into tbl2(refTbl1) values(@newIdTbl1);
        if @@TRANCOUNT>0 -- Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
        begin 
            commit transaction
            set @commited=1;
        end
    end try
    begin catch    
	    if @@TRANCOUNT>0 rollback transaction			
    end catch;    
END;
GO

declare @x as int;
exec insertName 'jjkkll',@x output;
select @x;
}}}

== Dates examples ==
{{{#!highlight sql 
create table datesx ( date1 datetime2 , date2 datetime2 );
insert into datesx (date1,date2)
values('2014-01-01T00:01:02','2014-04-11T00:01:02')
select * from datesx
select * from datesx where date1 between '2013-12-01T00:00:00Z' and '2014-01-01T00:01:02Z'
select * from datesx where date1 between '2013-12-01T00:00:00Z' and '2014-01-01T00:01:00Z'
}}}

== Shrink log file ==
 * https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15
 * https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-ver15#TsqlProcedure
{{{
USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

USE [master]
GO
ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'DATABASENAME_Log', SIZE = 512000KB , FILEGROWTH = 0)
GO
----
USE [dbx];
GO
select * from sys.database_files
go
DBCC SHRINKFILE ('dbx_Log', 20);
GO
}}}

== Show tables ==
{{{
USE AdventureWorks2012;  
GO  
EXEC sp_tables  @table_name = '%',   @table_owner = 'dbo',    @table_qualifier = 'AdventureWorks2012';  
GO
}}}