SQL Easy Learning

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @myDateTime DATETIME ) RETURNS INT       AS   BEGIN   DECLARE @rtDate INT     SET @rtDate = CASE WHEN MONTH(@myDateTime)           IN (1, 3, 5, 7, 8, 10, 12) THEN 31           WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30     ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0             AND               YEAR(@myDateTime) % 100 != 0)           OR         (YEAR(@myDateTime) % 400 = 0)             THEN 29       ELSE 28 END     END   RETURN @rtDate   END

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[IsWeekEnd]                (@dtDate DATETIME) RETURNS BIT AS   BEGIN     DECLARE  @IsWeekEnd BIT         SELECT @IsWeekEnd = CASE ((Datepart(dw,@dtDate) + @@DATEFIRST - 7)%7)                           WHEN 1 THEN 1                           WHEN 0 THEN 1                           ELSE 0                         END         RETURN @IsWeekEnd   END

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[udf_GetDate]                (@Date   DATETIME,                 @fORMAT VARCHAR(80)) RETURNS NVARCHAR(80) AS   BEGIN     DECLARE  @Dateformat INT         DECLARE  @ReturnedDate VARCHAR(80)         DECLARE  @TwelveHourClock INT         DECLARE  @Before INT         DECLARE  @pos INT         DECLARE  @Escape INT                         -- (C) Pinal Dave http://www.SQLAuthority.com     SELECT @ReturnedDate = 'error! unrecognised format ' + @format         SELECT @DateFormat = CASE @format                           ...

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create function [dbo].[GetTenure] (@dateOfJoin datetime) Returns Varchar(200) AS Begin DECLARE @today datetime, @thisYearJoinDay datetime DECLARE @years int, @months int, @days int set @today = GETDATE() set @thisYearJoinDay = DATEADD(year, DATEDIFF(year, @dateOfJoin, @today), @dateOfJoin) set @years = DATEDIFF(year, @dateOfJoin, @today) - (CASE WHEN @thisYearJoinDay > @today THEN 1 ELSE 0 END) set @months = MONTH(@today - @thisYearJoinDay) - 1 set @days = DAY(@today - @thisYearJoinDay) - 1 return cast(@years as varchar) +'-'+ cast(@months as varchar)+'-'+cast(@days as varchar) --SELECT @years as years, @months as months, @days as days End

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[FN_YEARDIFF] (@STD AS DATETIME, @FND AS DATETIME) RETURNS VARCHAR(16) AS BEGIN             declare @SD     AS INT             declare @SM    AS INT             declare @SY     AS INT             declare @FD     AS INT             declare @FM     AS INT             declare @FY     AS INT             DECLARE @YVAR        AS INT             DECLARE @MVAR       AS INT             DECLARE @DIFF          AS VARCHAR(16)             DECLARE @EM            AS INT ...

update change_users_login 'update_one','Test','Test

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_GeneratePassword](@Length int, @PasswordTemp varchar(100) output) AS BEGIN DECLARE @RandomID varchar(32) DECLARE @counter smallint DECLARE @RandomNumber float DECLARE @RandomNumberInt tinyint DECLARE @CurrentCharacter varchar(1) DECLARE @ValidCharacters varchar(255) SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' DECLARE @ValidCharactersLength int SET @ValidCharactersLength = len(@ValidCharacters) SET @CurrentCharacter = '' SET @RandomNumber = 0 SET @RandomNumberInt = 0 SET @RandomID = '' SET NOCOUNT ON SET @counter = 1 WHILE @counter < (@Length + 1) BEGIN         SET @RandomNumber = Rand()         SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))         SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1) ...

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_SearchTables]  @Tablenames VARCHAR(500) ,@SearchStr NVARCHAR(60) ,@GenerateSQLOnly Bit = 0 AS /*     Parameters and usage     @Tablenames        -- Provide a single table name or multiple table name with comma seperated.                         If left blank , it will check for all the tables in the database     @SearchStr        -- Provide the search string. Use the '%' to coin the search.                         EX : X%--- will give data staring with X                              %X--- will give data ending with X                              %X%--- wil...

CREATE PROC [dbo].[InsertGenerator] (@tableName varchar(100)) as   --Declare a cursor to retrieve column specific information   --for the specified table DECLARE cursCol CURSOR FAST_FORWARD FOR   SELECT column_name,data_type FROM information_schema.columns       WHERE table_name = @tableName OPEN cursCol DECLARE @string nvarchar(3000) --for storing the first half                                  --of INSERT statement DECLARE @stringData nvarchar(3000) --for storing the data                                      --(VALUES) related statement DECLARE @dataType nvarchar(1000) --data types returned                                    --for respective columns SET @string='INSERT '+@ta...

SELECT DB_NAME(database_id) AS DatabaseName, CAST([Name] AS varchar(20)) AS NameofFile, CAST(physical_name AS varchar(100)) AS PhysicalFile, type_desc AS FileType, ((size * 8)/1024) AS FileSize, MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED' WHEN max_size = 0 THEN 'NO_GROWTH' WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15)) ELSE 'Unknown' END, SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED' WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10)) ELSE 'Unknown' END, Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE' WHEN growth > 0 THEN ((growth * 8)/1024) ELSE 'Unknown' END, GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE' WHEN is_percent_growth = 0 THEN 'MBs' ELSE 'Unknown' END FROM master.sys.master_f...

SQL Server Agent Tables ID Purpose SQL Server 2000 Table SQL Server 2005 Table 1 Primary table for job related information MSDB.dbo.sysjobs MSDB.dbo.sysjobs 2 Entry for each step in a specific job MSDB.dbo.sysjobsteps MSDB.dbo.sysjobsteps 3 Schedule(s) for each job MSDB.dbo.sysjobschedules MSDB.dbo.sysjobschedules 4 Local or remote servers where the job executes MSDB.dbo.sysjobservers MSDB.dbo.sysjobservers 5 Historical record of the job's execution MSDB.dbo.sysjobhistory MSDB.dbo.sysjobhistory 6 Current job status, next run date\time, queued date, etc. Not applicable MSDB.dbo.sysjobactivity 7 Historical job step log information for all job steps configured to write to this table Not applicable MSDB.dbo.sysjobstepslogs SQL Server 2000 and 2005 Code Snippets SQL Server 2000 Code Snippet -- Variable Declarations  DECLARE  @PreviousDate  datetime  DECLARE  @Year  VAR...