• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Select the values from the first column in all tables in my database

Hi,

I have a database with a number of tables, I want to return the values of the first column in each table.

To illustrate, if there are, say, 3 tables in the database MyDB called Table1, Table2, Table3 with columns as follows:

Table1 has columns LastName, FirstName, Title
Table2 has columns Street, City, Country
Table3 has columns Age, Ethnicity, Gender, AccommodationType

Then I want the equivalent of doing:

Select LastName from Table1
Select Street from Table2
Select Age from Table3
etc.

Of course, in the real database there are about fifty tables and each has its own ideosyncrasies and column names are different in each and of differing types. Can I automate this using schema objects like 'Column_Name' etc in a loop of some sort for each table in the DB?

Thanks
Iain
0
iaing1000
Asked:
iaing1000
  • 4
  • 3
  • 3
2 Solutions
 
chapmandewCommented:
you can run this query to get them..

select column_name from information_schema.columns
where table_name in('table1', 'table2', 'table3')
and ordinal_position = 1
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Use the query below as the Select query for your cursor and execute the statement for each and every values:

Hope this helps
SELECT 'SELECT ' + CO.COLUMN_NAME + ' FROM ' + TA.TABLE_SCHEMA + '.' + TA.TABLE_NAME + ';'
FROM INFORMATION_SCHEMA.TABLES TA, INFORMATION_SCHEMA.COLUMNS CO
WHERE TA.TABLE_NAME = CO.TABLE_NAME
AND TA.TABLE_SCHEMA = CO.TABLE_SCHEMA
AND CO.ORDINAL_POSITION = 1

Open in new window

0
 
iaing1000Author Commented:
Hi,

Thanks for the fast response.

This only returns the actual names of the columns themselves. If you can imagine the result of the select staements I've used to illustrate the question, that is what I'm after.

Also, I won't have time to explicitly outline the names of the tables as you've outlined, since there will be a lot of them. Possibly increasing from 50 to 900 ,so it all needs to be automated with no explicit reference to the tables or columns themselves

Thanks
Iain
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
chapmandewCommented:
so, you want something to select ALL records from the "first" column of all of your tables?
0
 
iaing1000Author Commented:
Hi rrjegan,

I'll have a look at this, it looks useful.

It seems to return the actual SELECT statements themselves though. I'm after the result of running each of these resultant SELECTS one after the other, I'm trying to find a way of re-jigging your SQL to do this because it must be close.

 The result I'm after will be the same as pasting this...

Select LastName from Table1
Select Street from Table2
Select Age from Table3
etc.

...into the New Query area and clicking Execute

Thanks
Iain


0
 
iaing1000Author Commented:
Hi chapmandew,

Yes that's exactly right,

Thanks for being so quick too,

Iain
0
 
chapmandewCommented:
declare @sql nvarchar(4000), @tablename nvarchar(255), @columnname nvarchar(255)

DECLARE  CursorTemplate CURSOR
FAST_FORWARD FOR       
      
select table_name, column_name from INFORMATION_SCHEMA.COLUMNS
where ordinal_position = 1

OPEN CursorTemplate

FETCH NEXT FROM CursorTemplate
INTO      @tablename, @columnname

WHILE (@@FETCH_STATUS = 0)
BEGIN
      set @sql = 'select ' + @columnname + ' from ' + @tablename
      exec sp_executesql @sql

      FETCH NEXT FROM CursorTemplate
      INTO      @tablename, @columnname

END

CLOSE CursorTemplate
DEALLOCATE CursorTemplate
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Test this one out which works as you explained:

DECLARE @sql nvarchar(1000);
DECLARE select_cur CURSOR FOR 
SELECT 'SELECT ' + CO.COLUMN_NAME + ' FROM ' + TA.TABLE_SCHEMA + '.' + TA.TABLE_NAME + ';'
FROM INFORMATION_SCHEMA.TABLES TA, INFORMATION_SCHEMA.COLUMNS CO
WHERE TA.TABLE_NAME = CO.TABLE_NAME
AND TA.TABLE_SCHEMA = CO.TABLE_SCHEMA
AND CO.ORDINAL_POSITION = 1;
 
    OPEN select_cur;
 
    FETCH NEXT FROM select_cur
    INTO @sql;
 
    WHILE @@FETCH_STATUS = 0
       BEGIN
          EXEC sp_executesql @sql;
          
          FETCH NEXT FROM select_cur
          INTO @sql;
       END;
    CLOSE select_cur;
    DEALLOCATE select_cur;

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
A small change in SELECT query:
DECLARE @sql nvarchar(1000);
DECLARE select_cur CURSOR FOR 
SELECT 'SELECT ' + CO.COLUMN_NAME + ' FROM ' + TA.TABLE_SCHEMA + '.' + TA.TABLE_NAME + ';'
FROM INFORMATION_SCHEMA.TABLES TA, INFORMATION_SCHEMA.COLUMNS CO
WHERE TA.TABLE_NAME = CO.TABLE_NAME
AND TA.TABLE_SCHEMA = CO.TABLE_SCHEMA
AND CO.ORDINAL_POSITION = 1
AND TA.TABLE_TYPE = 'BASE TABLE';
 
    OPEN select_cur;
 
    FETCH NEXT FROM select_cur
    INTO @sql;
 
    WHILE @@FETCH_STATUS = 0
       BEGIN
          EXEC sp_executesql @sql;
          
          FETCH NEXT FROM select_cur
          INTO @sql;
       END;
    CLOSE select_cur;
    DEALLOCATE select_cur;

Open in new window

0
 
iaing1000Author Commented:
Hi,

Those both worked perfectly!

Thank you both ever so much,

Points split in a minute or two,

Thanks
Iain
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now