ALTER TABLE [dbo].[Message] WITH CHECK ADD CONSTRAINT [CK_Message] CHECK (([InReplyToId] IS NULL OR [dbo].[CheckInReplyToMessageId]([InReplyToId])>=(0))) GO ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [CK_Message] CREATE FUNCTION [dbo].[CheckInReplyToMessageId] ( @MessageId BigInt ) RETURNS bit AS BEGIN DECLARE @RetVal bit SET @RetVal = (SELECT MessageId FROM Message WHERE MessageId = @MessageId) RETURN isnull(@RetVal, 0) END
Tag Archive for tsql
TSQL Function and Check Constraint to Ensure that a Parent Value is Present in the Table
Extract logins default db
select 'exec sp_defaultdb '''+ name + ''',''' + dbname + '''' from syslogins where dbname is not null
sp_ColDefinition or writing upsert SP in a snap
use master
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__ColDefinition]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp__ColDefinition]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_SizePrecScale]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_SizePrecScale]
GO
CREATE function fn_SizePrecScale(
@Type varchar(50),
@Length int,
@Prec int,
@Scale int)
returns varchar(100)
as
begin
DECLARE @RC varchar(100)
IF @Type in ('smalldatetime','datatime','text','image','bit','ntext','uniqueidentifier','bigint','int','smallint','tinyint','money','smallmoney')
SET @RC = @Type
ELSE IF @Type in('decimal','numeric')
SET @RC = @Type + '(' + cast(@Length as varchar) + ') [' + cast(@Prec as varchar) + ',' + cast(@Scale as varchar) + ']'
ELSE
SET @RC = @Type + '(' + cast(@Length as varchar) + ')'
RETURN(@RC)
end
GO
CREATE PROC dbo.sp__ColDefinition
@ObjectName sysname
AS
BEGIN
DECLARE @xtype char(2)
SELECT @xtype = xtype FROM sysobjects WHERE name = @ObjectName
IF @xtype not in('U','FN','V','P')
begin
RAISERROR ('L''objet n''existe pas dans le catalogue', 16, 1)
RETURN
end
IF @xtype in ('U','V')
SELECT
[DECLARE] = '@' + C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ',',
C.name + ',' AS [INSERT INTO],
'@' + C.name + ',' AS [VALUES],
C.name + ' = @' + C.name + ',' AS [SET],
'@' + C.name + ' = ' + C.name + ',' AS [SELECT]
from sysobjects O, syscolumns C, systypes T
where O.id = C.id and C.xtype = T.xtype
and O.name = @ObjectName and T.xtype = T.xusertype
order by C.colorder
ELSE
SELECT
[DECLARE] = C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ','
from sysobjects O, syscolumns C, systypes T
where O.id = C.id and C.xtype = T.xtype
and O.name = @ObjectName and T.xtype = T.xusertype
order by C.colorder
END
TSQL to Start a Job Programatically
use MSDB execute SP_Start_job 'JobName'
Basic TSQL Cursor Syntax
declare @EntityName as varchar(100) declare DepNameCursor Cursor FAST_FORWARD FOR select top 100 MailName from DepNameExt OPEN DepNameCursor FETCH NEXT FROM DepNameCursor INTO @EntityName WHILE @@Fetch_Status = 0 BEGIN INSERT INTO [UDC].[dbo].[Entity] ([Name]) VALUES (@EntityName) FETCH NEXT FROM DepNameCursor INTO @EntityName END CLOSE DepNameCursor DEALLOCATE DepNameCursor
ASP show a sql table
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset")
Conn.Open Application("ConnectionString")
Dim temp
Dim i
temp =0
response.write "<table>"
do while not rst.eof
For i = 0 To rst.fields.count - 1
If temp = 0 Then
If i = 0 then response.write "<tr><td></td>"
response.write "<th>" & rst(i).name & "</th>"
If i = rst.fields.count - 1 then response.write "</tr>"
End If
Next
For i = 0 To rst.fields.count - 1
If i = 0 then response.write "<tr><td>" & temp & "</td>"
response.write "<td>" & rst(i)
response.write("</td>")
If i = rst.fields.count - 1 then response.write "</tr>"
Next
temp = temp + 1
rst.movenext
loop
response.write "</table>"
rst.close
set rst=nothing
%>
TSQL snippets for snippetsEmu
if !exists('loaded_snippet') || &cp
finish
endif
Snippet $proc IF EXISTS (<CR>SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES<CR>
WHERE ROUTINE_NAME = '<{"name"}>'<CR>
AND ROUTINE_SCHEMA = 'dbo'<CR>AND ROUTINE_TYPE = 'PROCEDURE'<CR><BS>)<CR>
BEGIN<CR>DROP PROCEDURE dbo.<{"name"}><CR>
PRINT 'Dropped dbo.<{"name"}>'<CR>
END<CR>GO<CR><CR>CREATE PROCEDURE dbo.<{"name"}> (<CR>
/*<CR> Sample calls:<CR>
<BS><Tab>EXEC <{"name"}><CR><BS>*/<CR>)<CR>AS BEGIN<CR>
<Tab>SET NOCOUNT ON<CR><{}><CR>END<CR>GO<CR><CR>IF @@ERROR = 0<CR>
PRINT 'Created dbo.<{"name"}>'<CR><BS>GO<CR>
Snippet $func IF EXISTS (<CR>SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES<CR>
WHERE ROUTINE_NAME = '<{"name"}>'<CR>
AND ROUTINE_SCHEMA = 'dbo'<CR>AND ROUTINE_TYPE = 'FUNCTION'<CR><BS>)<CR>
BEGIN<CR>DROP FUNCTION dbo.<{"name"}><CR>
PRINT 'Dropped dbo.<{"name"}>'<CR>
END<CR>GO<CR><CR>CREATE FUNCTION dbo.<{"name"}> (<CR>
/*<CR> Sample calls:<CR>
<BS><Tab>SELECT dbo.<{"name"}>()<CR><BS>*/<CR>)<CR>
RETURNS <{"return_type"}><CR>AS BEGIN<CR>
<Tab><{}><CR>END<CR>GO<CR><CR>IF @@ERROR = 0<CR>
PRINT 'Created dbo.<{"name"}>'<CR><BS>GO<CR>
Snippet $view IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = '<{"name"}>') BEGIN<CR>
DROP VIEW dbo.<{"name"}><CR>
PRINT 'Dropped dbo.<{"name"}>'<CR>
END<CR>GO<CR><CR>CREATE VIEW dbo.<{"name"}><CR>AS<CR>
/* Sample calls:<CR>
<Tab>SELECT * FROM <{"name"}><CR><BS>*/<CR><{}><CR>GO<CR>
<CR>IF @@ERROR = 0<CR>
PRINT 'Created dbo.<{"name"}>'<CR><BS>GO<CR>
Snippet $table IF EXISTS (<CR>SELECT 1 FROM INFORMATION_SCHEMA.TABLES<CR>
WHERE TABLE_NAME = '<{"name"}>'
AND TABLE_SCHEMA = 'dbo'<CR><BS>)<CR>BEGIN<CR>
DROP TABLE dbo.<{"name"}><CR>
PRINT 'Dropped dbo.<{"name"}>'<CR>
END<CR>GO<CR><CR>CREATE TABLE dbo.<{"name"}> (<CR>
<{}><CR>)<CR>GO<CR><CR>IF @@ERROR = 0<CR>
PRINT 'Created dbo.<{"name"}>'<CR><BS>GO<CR>
Snippet $sum SUM(<{"field"}>) AS <{"field"}>,<{}>
Snippet $vc varchar(<{"size"}>)<{}>
Snippet $c char(<{"size"}>)<{}>
Snippet $d decimal(<{"size"}>,<{"precision"}>)<{}>
Snippet $tv DECLARE @<{"name"}> table (<CR><{}><CR>)
Snippet $tt CREATE TABLE #<{"name"}> (<CR><{}><CR>)
TSQL keyword search
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%keyword%'
Category: Uncategorized |
Tags: tsql
TSQL last second of day
DATEADD(s, -1, DATEADD(d, 1, DATEADD(d, DATEDIFF(d, 0, @date), 0)))