Different Way of Alias in column

There are different ways to alias a column. Here are some possible ways that work in SQL Server.
01. select
02. 1 as number,
03. 1 number,
04. 1 "number",
05. 1 'number',
06. 1number,
07. 1"number",
08. 1'number',
09. 1 as "number",
10. 1 as 'number',
11. 1.number,
12. 1."number",
13. 1.'number',
14. 1 as [number],
15. 1 [number],
16. 1.[number],
17. 1[number]

Bulk Insert in sql server 2000

Bulk Insert-Copies a data file into a database table or view in a user-specified format.
Eg-
BULK INSERT test
FROM 'd:\testabc.txt'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n'
)

Break vs Return

Suppose you have a set of codes and when a particular condition is true, you want to terminate or switch to another set of code, you can use Break commnad. I see sometimes people use return commnad for this. But there is a significant difference between these two. Consider this example
Usage of BREAK (select will be executed)
declare @j int
set @j=1
while 1=1
begin
print @i
if @j>10 break -It Terminate the Current Block
set @j=@j+1
end
select @j

declare @i int
set @i=1
while 1=1
begin
print @i
if @i>10 return It Terminate the Entire Block
set @i=@i+1
end
.select @i
Break Command-it just terminate the current block (created by if clause, while loop etc), but Return Command- will terminate the entire block

Retrieving all the table Name and view name of specific Database

use ABC
select * from information_schema.tables-Retrieve Tables

select * from information_schema.view --retrieve Views

OPENROWSET

OPENROWSET-Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

Eg-SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=F:\', testing#txt)

List of datatypes supported in SQL Server

List of datatypes supported in SQL Server-
exec sp_datatype_info in sql server 2000
exec sp_datatype_info_90 in sql server 2005
exec sp_datatype_info_100 in sql server2008

use of cursor to print all Stored procedure of Database

DECLARE @procName varchar(100)

DECLARE RetProcName CURSOR FOR select [Name] from sysobjects where type='P'

OPEN RetProcName

FETCH NEXT FROM RetProcName INTO @procName

WHILE @@FETCH_STATUS = 0

BEGIN

exec sp_helptext @Procname

-- print

Fetch NEXT FROM RetProcName INTO @procName

END

CLOSE RetProcName

DEALLOCATE RetProcName