Change date, month in datetime datatype column

Suppose you want to change the date, month in datetime datatype column

Ex: 1966-09-01 00:00:00 ---->>>>  1966-01-09 00:00:00

Table Name:  TableName
Column Name: dob

update TableName set  dob=convert(datetime, convert(varchar(10), convert(varchar(4),year(dob))+'-'+right('0' +convert(varchar(2),day(dob)),2)+'-'+ right('0'+convert (varchar(2),month(dob)),2)),101)



Created by Vikas Jaiswal
Contact on vikasjaiswal.ctit@gmail.com

Search Text in SP Views Functions in SQL Server

Suppose we want to search "abc" text in Stored procedure, views, function in sql then use below query: 

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'abc', @notcontain = ''

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0 
or CHARINDEX(@notcontain,syscomments.text)<>0))

Searching and finding a string value in all columns in a SQL Server table

 

Below is the procedure to find string value in all columns in sql table

Create PROCEDURE [dbo].[sp_FindStringInTable] @stringToFind VARCHAR(100), @schema sysname, @table sysname
AS

DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(8000)

BEGIN TRY
   SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE'
   SET @where = ''

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = ''' + @schema + '''
   AND TABLE_NAME = ''' + @table + '''
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'

   EXEC (@cursor)

   OPEN col_cursor  
   FETCH NEXT FROM col_cursor INTO @columnName  

   WHILE @@FETCH_STATUS = 0  
   BEGIN  
       IF @where <> ''
           SET @where = @where + ' OR'

       SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + ''''
       FETCH NEXT FROM col_cursor INTO @columnName  
   END  

   CLOSE col_cursor  
   DEALLOCATE col_cursor

   SET @sqlCommand = @sqlCommand + @where
   --PRINT @sqlCommand
   EXEC (@sqlCommand)
END TRY
BEGIN CATCH
   PRINT 'There was an error. Check to make sure object exists.'
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3
   BEGIN
       CLOSE col_cursor  
       DEALLOCATE col_cursor
   END
END CATCH 

Example:

Here are some tests that were done against the Employee database.
Find the value 'John%' in the dbo.EmployeeList table.

EXEC sp_FindStringInTable 'John%', 'dbo', 'EmployeeList'

you will get rows having john in any column.

In this procedure you have to pass three parameters --- string to search, schema name, table name.
Sometimes, you do not know schema name for a table.

SELECT '['+SCHEMA_NAME(schema_id)+']'
AS SchemaTable
FROM sys.tables

Where namelike 'EmployeeList'

 

List Schema Name and Table Name for Database

 

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables

First letter upper case for each word in sql server

First letter upper case for each word in sql server

I have a table on my database. My table's name is "Test". I want to change data "Column1" as upper case first letter. For example;  Data for Column1 "AbC COMPANY" as "Abc Company" To do this : Step1: Create a function CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) RETURNS VARCHAR(4000) AS BEGIN DECLARE @Index INT DECLARE @Char CHAR(1) DECLARE @PrevChar CHAR(1) DECLARE @OutputString VARCHAR(255) SET @OutputString = LOWER(@InputString) SET @Index = 1 WHILE @Index <= LEN(@InputString) BEGIN SET @Char = SUBSTRING(@InputString, @Index, 1) SET @PrevChar = CASE WHEN @Index = 1 THEN ' ' ELSE SUBSTRING(@InputString, @Index - 1, 1) END IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(') BEGIN IF @PrevChar != '''' OR UPPER(@Char) != 'S' SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char)) END SET @Index = @Index + 1 END RETURN @OutputString END GO   Step 2:  update table set column=[dbo].[InitCap](column);

Create, Mapping Linked Server in sql

Script to Create the Linked Server
EXEC master.dbo.sp_addlinkedserver @server = N’dsql2k’, @srvproduct=N”, @provider=N’SQLOLEDB’, @datasrc=N’testserver’
GO

Mapping of Users
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’dsql2k’, @locallogin = N’sa’, @useself = N’False’, @rmtuser = N’sa’, @rmtpassword = N’XXXXXX’
GO

get linked server list in sql

--------------------------------------------------

Get list of Linked Server in SQL

SELECT * FROM sys.Servers 

------------------------------------------------------- 

ADD Linked Server in SQL

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 

For more information 

https://msdn.microsoft.com/en-us/library/ms190479.aspx 

Example:

sp_addlinkedserver  @server=  'LINK_Database',  @srvproduct='' 
     , @provider=  'SQLNCLI'
    , @datasrc=  'server ip' 

ADD Linked Server in SQL

ADD Linked Server in SQL

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 

For more information 

https://msdn.microsoft.com/en-us/library/ms190479.aspx 

Example:

sp_addlinkedserver  @server=  'LINK_Database',  @srvproduct='' 
     , @provider=  'SQLNCLI'
    , @datasrc=  'server ip' 

--------------------------------------------------

Get list of Linked Server in SQL

SELECT * FROM sys.Servers 

Add a column, with a default value, to an existing table in SQL Server

Add a column, with a default value, to an existing table in SQL Server

Declare @SQL VarChar(1000)

SELECT @SQL = 'ALTER TABLE my_table ADD ' + @column + ' INT'

Exec (@SQL)

Sql temp table with except

Sql temp table with except

Create table #TableName (Column1 datatype1, Column2 datatype2)

insert into #TableName 

Select Column1, Column2 from Table1

Except

Select Column1, Column2 from Table2

Select * from  #TableName

Drop table #TableName

Query to get field name with datatype and size

Query to get field name with datatype and size

SELECT column_name as 'Column Name', data_type as 'Data Type', 

character_maximum_length as 'Max Length' 

FROM information_schema.columns 

FROM information_schema.columns

 

check whether a column has any letters or not

SELECT *
FROM    myTabet
WHERE   ISNUMERIC (myColumn) = 0

Remove white spaces in column value in sql

Remove white spaces in column value  in sql

update TableName set ColumnName= lTrim(rTrim(ColumnName))

Remove new line in column values in SQL Server

Remove new line in column values in SQL Server

declare @NewLine char(2)
set @NewLine=char(13)+char(10)

UPDATE TableName
SET ColumnName=Replace(ColumnName, @NewLine,'')
WHERE ColumnName like '%' +@NewLine +'%'

Date Formats in SQL

Date Format in SQL

Format #	Query (current date: 12/30/2006)(dd/mm/yyyy)	Sample
1	select convert(varchar, getdate(), 1)	12/30/06
2	select convert(varchar, getdate(), 2)	06.12.30
3	select convert(varchar, getdate(), 3)	30/12/06
4	select convert(varchar, getdate(), 4)	30.12.06
5	select convert(varchar, getdate(), 5)	30-12-06
6	select convert(varchar, getdate(), 6)	30 Dec 06
7	select convert(varchar, getdate(), 7)	Dec 30, 06
10	select convert(varchar, getdate(), 10)	12-30-06
11	select convert(varchar, getdate(), 11)	06/12/30
101	select convert(varchar, getdate(), 101)	12/30/2006
102	select convert(varchar, getdate(), 102)	2006.12.30
103	select convert(varchar, getdate(), 103)	30/12/2006
104	select convert(varchar, getdate(), 104)	30.12.2006
105	select convert(varchar, getdate(), 105)	30-12-2006
106	select convert(varchar, getdate(), 106)	30 Dec 2006
107	select convert(varchar, getdate(), 107)	Dec 30, 2006
110	select convert(varchar, getdate(), 110)	12-30-2006
111	select convert(varchar, getdate(), 111)	2006/12/30

Time Format in SQL

8 or 108	select convert(varchar, getdate(), 8)	00:38:54
9 or 109	select convert(varchar, getdate(), 9)	Dec 30 2006 12:38:54:840AM
14 or 114	select convert(varchar, getdate(), 14)	00:38:54:840

Compare Dates in SQL

Compare Dates in SQL

With Between
CAST(CONVERT (varchar,@FromDate,101) AS DATE) between CAST(CONVERT (varchar,DateFrom,101) AS DATE) and CAST(CONVERT (varchar,DateTo,101) AS DATE)

With Operator
CAST(Convert(varchar,DateOfJoining,101) AS DATE) > CAST(CONVERT (varchar,@FromDate,101) AS DATE)