.Net Pickles

A little knowledge is a dangerous thing - Proverb Stories -Story|Short Stories|Short Story|Stories for Kids

Below is the query used to find the modified tables,functions and stored procedures in last 1 day. SELECT   *   FROM   sys . objects WHERE   DATEDIFF ( D , modify_date ,   GETDATE ())   <  1 To find only modified functions in last one day below query is used SELECT * FROM sys . objects WHERE TYPE = 'fn' AND DATEDIFF ( D , modify_date , GETDATE ()) <  1 To find only modified tables in last one day below query is used SELECT * FROM sys . objects WHERE TYPE = 'U' AND DATEDIFF ( D , modify_date , GETDATE ()) <  1 To find only modified procedures in last one day below query is used SELECT * FROM sys . objects WHERE TYPE = 'P' AND DATEDIFF ( D , modify_date , GETDATE ()) <  1

In this article i am going to explain about the query used to to check uppercase letter is present in the string. Below is the query which i used to do this operation. It will return 1 if the upper case letter is there otherwise returns 0. DECLARE       @TestString VARCHAR ( 100 ) SET @TestString = 'Sarat' SELECT CASE WHEN BINARY_CHECKSUM ( @TestString ) = BINARY_CHECKSUM ( LOWER ( @TestString )) THEN 0 ELSE 1 END AS DoesContainUpperCase GO

In this article i am going to expalin the query used to count the no of characters in a string. Below is the query which i used to do this operation. First i have declared a variable and assigned some value to that variable. Then using CTE i found the count of each characters in the input string.  DECLARE @SplitList NVARCHAR ( 4000 ); SELECT   @SplitList = 'Count Howmany Characters InString' ; WITH     Listings ( Position , CharacterSymbol ) AS (         SELECT   1 , SUBSTRING ( @SplitList , 1 , 1 )         UNION ALL         SELECT s . Position + 1 , SUBSTRING ( @SplitList , s . Position + 1 , 1 )         FROM     Listings AS s         WHERE   s . Position <= LEN ( @SplitList ) - 1 ) SELECT   CASE WHEN s . CharacterSymbol = '' THEN 'Empty Space' ELSE s . CharacterSymbol END AS WCharacter , Count = COUNT ( s . CharacterSymbol ) FROM     Listings AS s GROUP BY s . CharacterSymbol

Identity column must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable. So it is not possible to add a varchar column as Identity Column. Alternative ways to add varchar column as Identity column: Alternate 1: By using computed column: Examlple: CREATE TABLE dbo . MyTable1 (     Id int NOT NULL PRIMARY KEY ,     CombinedId AS 'ABCD-' + CAST ( Id as varchar ( 16 )) ,     Name varchar ( 50 ) ) CREATE TABLE dbo.MyTable (     Id int NOT NULL PRIMARY KEY ,     PrefixField varchar( 16 ),     CombinedId AS PrefixField + CAST(Id as varchar( 16 )) ) Alternate 1: We can Increment alphanumeric column Below is the function used to Increment alphanumeric column CREATE FUNCTION dbo . fn_IncrementAlphaNumericString (       @string nvarchar ( 100 ) )   /***************************************************************** ** Name           :

In this article I am going to explain how to extract the filename from html column using sql function. Below is the function which i used to do this operation CREATE FUNCTION dbo . fn_getFilenames ( @InputHTML NVARCHAR ( MAX )) RETURNS @res TABLE ( pdf NVARCHAR ( MAX )) AS BEGIN -- assumes there are no single quotes or double quotes in the PDF filename DECLARE @i INT , @j INT , @k INT , @tmp NVARCHAR ( MAX ); SET @i = CHARINDEX ( N'.pdf' , @InputHTML ); WHILE @i > 0 BEGIN   SELECT @tmp = left( @InputHTML , @i + 3 );   SELECT @j = CHARINDEX ( '/' , REVERSE ( @tmp )); -- directory delimiter   SELECT @k = CHARINDEX ( '"' , REVERSE ( @tmp )); -- start of href   IF @j = 0 or ( @k > 0 and @k < @j ) SET @j = @k ;   SELECT @k = CHARINDEX ( '''' , REVERSE ( @tmp )); -- start of href (single-quote*)   IF @j = 0 or ( @k > 0 AND @k < @j ) SET @j = @k ;  

Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks.  One of these tasks may be the need to backup the database on your server.   This is not a big deal.  You could use SQL Server Management Studio to backup the database or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.  Below is the T-SQL query which i used to back up my database.

I would like to create a select query statement with autonumber.. like.. select Id,Name  from myTable will give me everything from table. The result I'd like to get is.. 1          data1 2          data2 3          data3 So i have used the below query SELECT   ROW_NUMBER ()   OVER  ( ORDER   BY  Name )   ,   *   FROM   myTable