Retrieve All the Column Names of SQL Table.

astrovirgin

Hi, I want to retrieve all the column names by a query of a table in SQL SERVER 2005. For Example: I Have a table: Table1, It has 5 columns: Column1,Column2,Column3,....... So, I want to write a query that will display all these column names. Table1 -------- Column1 Column2 Column3 Column4 Column5 I write this query to do the same:

Select column_name, data_type from user_tab_columns where table_name = user_details

But it is not working. Is this a right query or some mistakes are there.

Ashfield

astrovirgin wrote:

But it is not working. Is this a right query or some mistakes are there.

Well, if its not working.... Where to start? The error message you get is pretty obvious Invalid object name 'user_tab_columns'. I'll give you 1 hint (and there are several ways of doing this), look at information_schema.columns

Bob Ashfield Consultants Ltd

astrovirgin

Thanx. It works well.

PIEBALDconsult

The information_schema.columns technique only works for SQL Server. I use a lot of different databases, and I don't have the constraint that it be "in SQL". Here's a portion of my DatabaseAccessor[^]:

            System.Data.DataTable result = null ;

            try
            {
                result = this.ExecuteReader 
                ( 
                    string.Format 
                    ( 
                        "SELECT \* FROM \[{0}\] WHERE 0=1" 
                    ,
                        TableName
                    ) 
                ).GetSchemaTable() ;
                
                result.TableName = "Columns" ;

Ashfield

Fair enough, although the OP did specify sql server. I suspect (although I have never tried it) that you could use SMO as well.

Bob Ashfield Consultants Ltd

PIEBALDconsult

Ashfield wrote:

the OP did specify sql server

Oh, yes, absolutely, and he wanted it to be an SQL statement too, but after finding one way for SQL Server, another way for Oracle, another way for Ingres, and then facing MySQL, Caché, FireBird, Access, etc. I realized I already had a way that works with all of them. I aim for database agnosticism. (I do, however, prefer SQL Server.)