Cybercrux

Everything is achievable through technology

Tips & Tricks

1.To get a (store procedure,function,table,etc)  available in a database

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '% Get_Ltp %'

2. Get Procedure
sp_helptext 'Procedurename'

3.select column name in query
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'customTableName'

4.select row with serial number
SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS [SNO] , ID,Name,Time FROM table_name

5.select a row with comma separation

DECLARE @Heroes TABLE (
[HeroName] VARCHAR(20)
)
INSERT INTO @Heroes ( [HeroName] )
VALUES ( 'Superman' ), ( 'Batman' ), ('Ironman'), ('Wolverine')
--Option 1
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr + ',','') + [HeroName] FROM @Heroes
SELECT @listStr AS [Output]
--Option 2
SELECT STUFF((SELECT ',' + [HeroName] FROM @Heroes FOR XML PATH('')), 1, 1, '') AS [Output]

6.SQL Injection Codes

‘ or ’1′=’1
‘ or ’1′=’1′ — ‘
‘ or ’1′=’1′ ({ ‘
‘ or ’1′=’1′ /* ‘

7.Alter Syntax
ALTER TABLE table_name
ADD column_name column-definition;
ALTER TABLE table_name
MODIFY column_name column_type;
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
ALTER TABLE table_name
RENAME TO new_table_name;
EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

8.Identity Insert syntax
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }
9.Select Last inserted Row Identity Value
SELECT CAST(SCOPE_IDENTITY() as int)

10. Select With case Sensitive
COLLATE Latin1_General_BIN

Advertisements

One thought on “Tips & Tricks

  1. install SQL in Windows 8
    Offline install code
    Dism /online /enable-feature /featurename:NetFx3 /All /Source:F:\sources\sxs /LimitAccess

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s