Monthly Archive:: May 2013

Sql query to get the modified tables,functions and stored procedures

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

Sql query to check uppercase letter is present in the string

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

Find count of each characters in a string in SQL Server

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. 

SQLServer IDENTITY Column with text

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

SQL query to extract file names from a html content

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

SQL Query to backup database in sql server

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

Sql Select statement with auto generated row id

I would like to create a select query statement with autonumber.. like.. select Id,Name frommyTable 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