Tech Share

I used think often whether data whatever we are storing is secured or not so I browsed about Encrypt function in Msdn I got this

Check the Code below.Given for Both Encrypt and Decrypt

--------------Encrypt & Decrypt----------------------
USE tophat
GO
DECLARE @x nvarchar(4000)
DECLARE @code varbinary(8000)
SET @code = dbo.Encrypt ('This is my First Enigma Program ')
PRINT @code
SET @x = dbo.decrypt (@code)
PRINT @x
---------------------------------------------------------

Read Users' Comments (0)

COALESCE is one of the inbuilt function in  SQLServer.

I never seen people use this wonderful funtion

COALESCE usually retrives non null values specified in its arguments.

we will get one column which compare with two columns and get non null column value.

Note:

1.     Things you should remember NULL is not '0'
2.      Before comparing convert all columns in same data type (Use CAST or Convert)
   

Read Users' Comments (0)

         Always it will be very hectic problem when we use Date in SQL queries with The database used in COBOL because the Date format is different.So I've done little homework on all formats of dates.
And I'll try to explain all date manipulation function.

List of all Date formats in SQLserver2005

Read Users' Comments (0)

I had Database called Tophat which has 8GB of data.And I had a Column value and my database has 450 tables
So how can I find out the particular column and table name

So I searched in Net and I got the solution


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Purpose: To search all columns of all tables for a given search string


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results

END


To execute

exec [SearchAllTables] 'AHN, JAMES'

'AHN, JAMES' should be the field you want to search in the database

Hope this help someone .

Read Users' Comments (0)