An A-Z Index of the SQL Server 2005 database

Aggregate - CREATE AGGREGATE
- DROP AGGREGATE
Application Role - CREATE APPLICATION ROLE
- ALTER APPLICATION ROLE
- DROP APPLICATION ROLE
Assembly - CREATE ASSEMBLY
- ALTER ASSEMBLY
- DROP ASSEMBLY
ALTER AUTHORIZATION

BACKUP
BACKUP CERTIFICATE
BEGIN [DIALOG [CONVERSATION]]

Certificate - ALTER CERTIFICATE
- CREATE CERTIFICATE
- DROP CERTIFICATE
CHECKPOINT
COMMIT
Contract - CREATE CONTRACT
- DROP CONTRACT
Credential - CREATE CREDENTIAL
- ALTER CREDENTIAL
- DROP CREDENTIAL

Database - CREATE DATABASE
- ALTER DATABASE
- DROP DATABASE
DBCC CHECKALLOC - Check consistency of disk allocation.
DBCC CHECKCATALOG - Check catalog consistency
DBCC CHECKCONSTRAINTS - Check integrity of table constraints.
DBCC CHECKDB - Check allocation, and integrity of all objects.
DBCC CHECKFILEGROUP - Check all tables and indexed views in a filegroup.
DBCC CHECKIDENT - Check identity value for a table.
DBCC CHECKTABLE - Check integrity of a table or indexed view.
DBCC CLEANTABLE - Reclaim space from dropped variable-length columns.
DBCC dllname - Unload a DLL from memory.
DBCC DROPCLEANBUFFERS - Remove all clean buffers from the buffer pool.
DBCC FREE... CACHE - Remove items from cache.
DBCC HELP - Help for DBCC commands.
DBCC INPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC OPENTRAN - Display information about recent transactions.
DBCC OUTPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC PROCCACHE - Display information about the procedure cache
DBCC SHOW_STATISTICS - Display the current distribution statistics
DBCC SHRINKDATABASE - Shrink the size of the database data and log files.
DBCC SHRINKFILE - Shrink or empty a database data or log file.
DBCC SQLPERF - Display transaction-log space statistics. Reset wait and latch statistics.
DBCC TRACE... - Enable or Disable trace flags
DBCC UPDATEUSAGE - Report and correct page and row count inaccuracies in catalog views
DBCC USEROPTIONS - Return the SET options currently active
DBCC deprecated commands
DECLARE
Default - CREATE DEFAULT
- DROP DEFAULT
DELETE
DENY - DENY Object permissions
- DENY User/Role permissions
Endpoint - CREATE ENDPOINT
- ALTER ENDPOINT
- DROP ENDPOINT
Event - CREATE EVENT NOTIFICATION
- DROP EVENT NOTIFICATION
EXECUTE
EXECUTE AS

Fulltext Catalog - CREATE FULLTEXT CATALOG
- ALTER FULLTEXT CATALOG
- DROP FULLTEXT CATALOG
Fulltext Index - CREATE FULLTEXT INDEX
- ALTER FULLTEXT INDEX
- DROP FULLTEXT INDEX
Function - CREATE FUNCTION
- ALTER FUNCTION
- DROP FUNCTION

GO
GRANT - GRANT Object permissions
- GRANT User/Role permissions

Index - CREATE INDEX
- ALTER INDEX
- DROP INDEX
INSERT
iSQL -U user -P password -i script.sql -o logfile.log

Key - CREATE ASYMMETRIC KEY
- ALTER ASYMMETRIC KEY
- DROP ASYMMETRIC KEY
- CREATE SYMMETRIC KEY
- OPEN SYMMETRIC KEY
- CLOSE SYMMETRIC KEY
- ALTER SYMMETRIC KEY
- DROP SYMMETRIC KEY
KILL
KILL QUERY NOTIFICATION
KILL STATS JOB

Login - CREATE LOGIN
- ALTER LOGIN
- DROP LOGIN

Master Key - CREATE MASTER KEY
- ALTER MASTER KEY
- BACKUP MASTER KEY
- DROP MASTER KEY
- RESTORE MASTER KEY
- ALTER SERVICE MASTER KEY
- BACKUP SERVICE MASTER KEY
- RESTORE SERVICE MASTER KEY
Message Type - CREATE MESSAGE TYPE
- ALTER MESSAGE TYPE
- DROP MESSAGE TYPE

Partition Function - CREATE PARTITION FUNCTION
- ALTER PARTITION FUNCTION
- DROP PARTITION FUNCTION
Partition Scheme - CREATE PARTITION SCHEME
- ALTER PARTITION SCHEME
- DROP PARTITION SCHEME
Procedure - CREATE PROCEDURE
- ALTER PROCEDURE
- DROP PROCEDURE

Queue - CREATE QUEUE
- ALTER QUEUE
- DROP QUEUE

Remote Service Binding - CREATE REMOTE SERVICE BINDING
- ALTER REMOTE SERVICE BINDING
- DROP REMOTE SERVICE BINDING

RESTORE - RESTORE DATABASE Complete
RESTORE DATABASE Partial
RESTORE DATABASE Files
RESTORE LOGS
RESTORE DATABASE_SNAPSHOT
RESTORE FILELISTONLY - List database and log files
RESTORE HEADERONLY - List backup header info
RESTORE LABELONLY - Media info
RESTORE REWINDONLY - Rewind and close tape device
RESTORE VERIFYONLY
REVERT
REVOKE - REVOKE Object permissions
- REVOKE User/Role permissions
Role - CREATE ROLE
- ALTER ROLE
- DROP ROLE
ROLLBACK
Route - CREATE ROUTE
- ALTER ROUTE
- DROP ROUTE

Schema - CREATE SCHEMA
- ALTER SCHEMA
- DROP SCHEMA
SELECT
SEND
SERVERPROPERTY
Service - CREATE SERVICE
- ALTER SERVICE
- DROP SERVICE
SESSION_USER
SESSIONPROPERTY
SET @local_variable
SET
SHUTDOWN
Signature - ADD SIGNATURE
- DROP SIGNATURE
Statistics - CREATE STATISTICS
- UPDATE STATISTICS
- DROP STATISTICS

Synonym - CREATE SYNONYM
- DROP SYNONYM

Table - CREATE TABLE
- ALTER TABLE
- DROP TABLE
- TRUNCATE TABLE
Transaction - BEGIN DISTRIBUTED TRANSACTION
- BEGIN TRANSACTION
- COMMIT TRANSACTION
Trigger - CREATE TRIGGER
- ALTER TRIGGER
- ENABLE TRIGGER
- DISABLE TRIGGER
- DROP TRIGGER
Type - CREATE TYPE
- DROP TYPE

UNION
UPDATE
User - CREATE USER
- ALTER USER
- DROP USER
USE

View - CREATE VIEW
- ALTER VIEW
- DROP VIEW

XML Schema Collection - CREATE XML SCHEMA COLLECTION
- ALTER XML SCHEMA COLLECTION
- DROP XML SCHEMA COLLECTION

Query to get the all field names of a table - sql

select A.name from sys.columns A join sys.tables B on A.object_id=b.object_id and B.name='tablename'

To find dupliactes in a table data - sql

here is the code for find out is there is any duplicates in the table data or not.

WITH T1 AS
(
Select szname, ROW_NUMBER()
OVER (PARTITION BY szname Order By szname) AS NUMBER From tblUsers
)
select * from T1 where Number>1

here Number is the number of dupliacation of a single record.

ways to substring a string with a delimiter - sql

here is the code for substring a string with specific delimiter in sql

DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = 'duplicate@Createrrrm@dddfdfddfdf',
@delimiter = '@'
DECLARE @textXML XML;
SELECT @textXML = CAST('' + REPLACE(@data, @delimiter, '') + '' AS XML);
--SELECT @textXML
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
/* doing it through a function */
select * from [dbo].fn_ParseDelimitedStrings ('duplicate@Createrrrm@dddfdfddfdf','@')

Function Code:

Create Function [dbo].[fn_ParseDelimitedStrings]

(@String nvarchar(3500), @Delimiter char(1))

Returns @Values Table

(

RowId int Not Null Identity(1,1) Primary Key

,Value nvarchar(255) Not Null

)

As

Begin

Declare @startPos smallint

,@endPos smallint

If (Right(@String, 1) != @Delimiter)

Set @String = @String + @Delimiter

Set @startPos = 1

Set @endPos = CharIndex(@Delimiter, @String)

While @endPos > 0

Begin

Insert @Values(Value)

Select LTrim(RTrim(SubString(@String, @startPos, @endPos - @startPos)))

-- remove the delimiter just used

Set @String = Stuff(@String, @endPos, 1, '')

-- move string pointer to next delimiter

Set @startPos = @endPos

Set @endPos = CharIndex(@Delimiter, @String)

End

Return

End

Why parseInt(08) & parseInt(09) is showing the value 0 ?

That's because "08" and "09" are invalid numbers, in octal.

The parseInt() function actually allows two arguments, the string to
parse and a radix, which is optional. This radix value allows you to
convert a binary (base 2), hexadecimal (base 16) or other base string to
a decimal integer. For example

parseInt("FF", 16);

returns 255. This is very useful for parsing things like HTML color values.

Most people aren't aware of the optional radix argument. The problem is
that if you leave it off, the function will doesn't necessarily assume
you want a decimal (base 10) conversion. Instead it checks the input
string (the first argument) and if it starts with "0x" it assumes it's a
hexadecimal value. If it starts with "0" - not followed by an "x" - it
takes it as an octal value. This follows the JavaScript convention for
numeric constants. If you code

var x = 0x18;
alert(x);

it will display 24 which is the decimal equivalent of the hex number
"18". Likewise,

var x = 014;
alert(x);

displays 12 which is the decimal value of the octal number "14".

As you should know, hexadecimal uses the digits 0-9 and the letters A-F,
16 in all. Octal is base 8, so only the digits 0-7 are valid. Hence,
"08" and "09" are not valid octal numbers and the function returns zero
just as it would for "xyz" in decimal - it's not a valid number.

To avoid this, always add the second argument, in this case

parseInt("08", 10);

returns 8 (decimal), as desired.