sum of dynamic tables column

[rony001]

I am new to sql server .I have recently downloaded Sqlserver 2008 and connected from vb.net . I am trying to create dynamic tables
with two columns such as
name,amount by passing table
names from text box .

CREATE TABLE " & TblName & "( [Name] TEXT(10), [Amount] TEXT(6))]

table1

name | amount
a | 40
b | 60

table2

name | amount
a | 150
b | 50

I want to display output like this
table name| total
table1 | 100
table2 | 200

I can retrieve table names using the below query

 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

I have tried the query
it displays a empty column

"SELECT 'SELECT ISNULL(SUM('+COLUMN_NAME + '),0) AS a FROM ' + TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'amount'"

I would like to know
Is it possible to write a query without specifying table name?
thanks

[BitBlt]

You are very close. The last dynamic query returns SQL statements, not results, like so:

Line 1: SELECT ISNULL(SUM(amount),0) AS a FROM table1
Line 2: SELECT ISNULL(SUM(amount),0) AS a FROM table2

Even if you execute them, it doesn't quite give you what you need. You need to also UNION them together and execute them to return a single result set. However, that still doesn't do the trick. The final step is to make sure that the dynamic SQL that's generated includes a string for the tables from the list. This one should do it:

SELECT 'SELECT ''' + TABLE_NAME + ''' as TableName, ISNULL(SUM('+COLUMN_NAME + '),0) AS a FROM ' + TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'amount'

The result looks like this:

Line 1: SELECT 'table1' as TableName, ISNULL(SUM(amount),0) AS a FROM table1
Line 2: SELECT 'table2' as TableName, ISNULL(SUM(amount),0) AS a FROM table2

Then, you just concatenate with appropriate UNION operators (depending on how many rows your dynamic query returns) and execute the result:

SELECT 'table1' as TableName, ISNULL(SUM(amount),0) AS a FROM table1
UNION
SELECT 'table2' as TableName, ISNULL(SUM(amount),0) AS a FROM table2

Your final result set looks like this:

TableName   a
table1      0
table2      0

Of course, if there are actual numbers you'll see them. :-)

Hope this helps! Happy coding!