Tag Archives: SQL 2005

Reading Database value in Javascript

Today while developing a page, we felt need to read database into javascript array. And we were confused if at all we can do this. So finally after googling on this we found a workaround. So I just thought of sharing this with you because I think this is really helpful.

[code]<script language="JavaScript" type="text/javascript">
var allRecords = new array();
var count=0;
<%While (NOT rs_yours.EOF)%>
allrecords[count]="<%= rs_yours.Fields.Item("foo1").Value %>"

This script will read the value from your datareader into javascript array.

Also if you want to read a single value from the server side into your javascript like this,

[code]var location='<%=Session["location"]%>’;[/code]

Hope this helps 🙂

SQL Execute with character string

Today I just got went though this, this helps you to execute a string in SQL. This is how you can execute a string in SQL,

[code]Declare @query Varchar(500)

Set @query = ‘Select * From Employees’

Exec (@query)[/code]

Another alternate option is as follows,

[code]EXEC (‘USE AdventureWorks; SELECT EmployeeID, Title FROM HumanResources.Employee;’)[/code]

This way you can execute a string in SQL.

This is extremely helpful when you need to add the Where clause based on some criteria. You can have your query in a string and based on that criteria you can append the clause in that string and finally execute the string at the end. This works perfectly.

However this does not work if you want to execute the query and get the result in the Dataset in your .Net application. For that you need to create a #Temp table execute the string and get the result in the #Temp table and then fire the select again on your #Temp table.

Note: Don’t forget to get the brackets ( & ) around your query else SQL will throw an error, Unrecognized stored procedure.

Copy table schema using SQL query

How will you copy table structure using an SQL Query?
This was the question I was asked in one of the inteview, after hearing this I first thought, Is there a way to copy only the table structure. I only knew the query to copy data along with its structure and take backup of tables.And I answered the same.

Later after finishing my interview I went home and googled on this, and was really surprised when I found these two queries.
So thought of sharing this.

Below are the queries which will only copy the table structure.
select * INTO newtable from oldtable where 1=2</code>

select Top 0 * into newtable from [oldtable][/code]

Hope this will help lot of them who are like me 🙂

List all tables in SQL

Often it is required where we need to get the list of tables in our database and the colums of the table with datatype.

Below is the query which will list all tables.

select * from sysobjects where xtype=’U’

Query which will return list of tables with columns and respective datatype.

from information_schema.columns
where table_name in (select name from sysobjects where xtype=’U’)
order by table_name

Enjoy 🙂