tsql
Microsoft Transact SQL (t-sql)
Show stored procedure or function code
Show CLR assemblies
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
Convert dates to string
Convert types: http://msdn.microsoft.com/en-us/library/ms187928%28SQL.90%29.aspx
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