Differences between revisions 1 and 14 (spanning 13 versions)

tsql

Microsoft Transact SQL (t-sql)

Show stored procedure or function code

   1 exec sp_helptext 'schema.storedProc'
   2 exec sp_helptext 'schema.function'

Show CLR assemblies

   1 -- get assembly_id
   2 select * from sys.assembly_modules

Extract SQL CLR assembly

http://serverfault.com/questions/139703/extracting-a-sqlclr-assembly

   1 -- reconfigure
   2 sp_configure 'show advanced options', 1;
   3 GO
   4 RECONFIGURE;
   5 GO
   6 sp_configure 'Ole Automation Procedures', 1;
   7 GO
   8 RECONFIGURE;
   9 GO
  10 
  11 -- extract assembly
  12 DECLARE @IMG_PATH VARBINARY(MAX)
  13 DECLARE @ObjectToken INT
  14 
  15 SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65824 and file_id=1
  16 
  17 EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
  18         EXEC sp_OASetProperty @ObjectToken, 'Type', 1
  19         EXEC sp_OAMethod @ObjectToken, 'Open'
  20         EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
  21         EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'c:\windows\temp\65824.dll', 2
  22         EXEC sp_OAMethod @ObjectToken, 'Close'
  23         EXEC sp_OADestroy @ObjectToken

Number seconds between two dates

   1 SELECT datediff(second, dateX1, GETDATE() )
   2 FROM tablex

Convert dates to string

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

   1 SELECT CONVERT(NVARCHAR(30), GETDATE(), 126)
   2 2009-05-01T14:18:12.430
   3 
   4 SELECT CONVERT(NVARCHAR(30), GETDATE(), 120)
   5 2009-05-01 14:18:12

Create stored procedure

   1 IF OBJECT_ID('insertName', 'P') IS NOT NULL 
   2 BEGIN
   3     DROP PROC insertName;
   4 END
   5 GO
   6 
   7 CREATE PROCEDURE insertName 
   8     @name as nvarchar(1024) , @commited as int output 
   9 AS
  10 BEGIN
  11     declare @newIdTbl1 as int;
  12     set @commited=0;
  13     begin try  
  14         begin transaction
  15         insert into tbl1(namex) values(@name);
  16         SELECT @newIdTbl1 = SCOPE_IDENTITY();
  17         insert into tbl2(refTbl1) values(@newIdTbl1);
  18         if @@TRANCOUNT>0 -- Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
  19         begin 
  20             commit transaction
  21             set @commited=1;
  22         end
  23     end try
  24     begin catch    
  25             if @@TRANCOUNT>0 rollback transaction                       
  26     end catch;    
  27 END;
  28 GO
  29 
  30 declare @x as int;
  31 exec insertName 'jjkkll',@x output;
  32 select @x;

Dates examples

   1 create table datesx ( date1 datetime2 , date2 datetime2 );
   2 insert into datesx (date1,date2)
   3 values('2014-01-01T00:01:02','2014-04-11T00:01:02')
   4 select * from datesx
   5 select * from datesx where date1 between '2013-12-01T00:00:00Z' and '2014-01-01T00:01:02Z'
   6 select * from datesx where date1 between '2013-12-01T00:00:00Z' and '2014-01-01T00:01:00Z'

Shrink log file

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