Return random rows using Sql query

Return random rows using Sql query

SELECT TOP 5 * FROM album ORDER BY NEWID()

List tables in ms sql server

List tables in ms sql server
=====================

For ms sql
sp_help

For my sql it is
show tables

List tables in ms sql server

List tables in ms sql server
=====================

For ms sql
sp_help

For my sql it is
show tables

List fields in ms sql server

List fields in ms sql server
=====================

for my sql:
describe tablename

for ms sql server:
sp_help tablename

list database in ms sql server

list database in ms sql server
=======================

Just type out following command in ms sql server query analyzer

sp_databases

For My sql type out show databases


if you want to use it use:
use nameofthedatabase

CREATE TABLE FROM ANOTHER TABLE IN MS SQL SERVER USING QUERY

CREATE TABLE FROM ANOTHER TABLE IN MS SQL SERVER USING QUERY

select * into newtable from oldtable

you will notice the newtable created... you may try out with various combination of joins etc.

The above is valid in ms sql server

Read the content of the storedprocedure by sql query

Read the content of the stored procedure by sql query


Get the list of the store procedure

SELECT * FROM sysobjects WHERE type = 'P' AND category = 0 ORDER BY name

Get the content of the stored procedure by specifying the name

SELECT text FROM syscomments WHERE id = (SELECT id FROM sysobjects WHERE name = 'my_StoreProcedureName') ORDER BY colid

play with ms sql server sysobjects

play with ms sql server sysobjects
===========================

Sometimes you may want to see the list of all the objects within a database. This can be easily achieved.

SELECT * FROM master..sysobjects


This below query list out all the store procedure within a database :

SELECT * FROM master..sysobjects WHERE TYPE='P'

You may try out the other options:

SELECT * FROM master..sysobjects WHERE TYPE='U'

SELECT * FROM master..sysobjects WHERE TYPE='X'

You may list out some system functions:

SELECT LEFT(name, 30) FROM master..sysobjects WHERE LEFT(name, 3) = 'fn_'

How to view structure of a table using Ms Sql Server

How to view structure of a table using Ms Sql Server
=========================================

You must be aware of describe table / desc table statement in oracle. By using this statement you can view the structure of a oracle table.

Same way in Ms Sql Server you can do the same by using
sp_columns store procedure.

Eg: sp_columns myTable

Generate insert statement from sql query

Generate insert statement from sql query
=================================

In some case you might have a requirement where you might want to generate insert statments directly by firing a sql query. I am sure you might need to use this especially for those who are into maintainance related work.

Anyway here is the process:

You might want to create a table : (I am assuming that you are already having a table)

create table myTable (
Id int,
name nvarchar(200))

select * from myTable

Insert some data into the table : (I am assuming that you are already having data into table)

insert into myTable values(1,'kingkong')
insert into myTable values(2,'godzilla')

Here is the main sql statement which generates the sql. The logic is straigh forward. Just to insert single quotes you might need to add '''' .. just change the query as per your own requirement



select convert(varchar(1000),'insert into myTable values(' + convert(varchar(10),Id) + ',' + '''' + convert(varchar(100),Name) + '''' + ')') from myTable

Result: (listing all the insert statement for a given table)

insert into myTable values(1,'kingkong')
insert into myTable values(2,'godzilla')

Same way like above you might want to generate custom update statement as well. You can also try it out by yourself

Sql Connection in Ruby

sql connection in Ruby
=================

Define a sql connectivity function:

def connection
#connection details like dsn, uid and password
$dsn = "POS"
$uid = ""
$pwd = ""
$c = ODBC.connect($dsn, $uid, $pwd)
end


def callConnection()
#opening database connection
connection()
end


#Typical login script
def onLoginClick(event)
$username="myUserName"
$password="myPassword"
$sql="select * from loginmaster where username='" + $username + "' and password='" + $password + "'"
#creating a connection object
$dsn = "POS"
$uid = ""
$pwd = ""
$c = ODBC.connect($dsn, $uid, $pwd)

$q=$c.prepare($sql)
$q.execute

if $q.fetch == nil then
#message will only show if you use wxpos - uncomment below if you are using wxwidget
# message_box("Incorrect Login", "Login",OK ICON_INFORMATION, self)
else
close()
end

end

Please Add Bookmark:
Connotea del.icio.us Digg it Furl Google ma.gnolia Netscape
Netvouz RawSugar reddit Shadows Simpy Spurl StumbleUpon Yahoo MyWeb