Select Column Names from SQL Server using PDO

Question

I am attempting to select column names from a SQL Server database with the following code:

 public function getColumns($table){

        $columns = array();
        $sql = "select column_name from information_schema.columns where table_name = 'dbo.myTable'";

        $stmt = $this->conn()->prepare($sql);

        try {

            if($stmt->execute()){

                $raw_column_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
                //
                //this is empty so we can stop right there
                //
                echo "<pre>";
                print_r($raw_column_data);
                echo "</pre>";



                }else{
                    echo "<pre>";
                    print_r($stmt->errorInfo());
                    echo "</pre>";
                }

                return $columns;

            } catch (Exception $e){

                    return $e->getMessage(); //return exception

            }
    }

Any ideas why the '$raw_column_data` array might be null? I have searched google high and low with no luck. I am almost positive that this is the correct syntax, and I am sure it is querying the database correctly.

John K. · Accepted Answer · 2011-02-02 18:41:46Z

1

Try removing the "dbo." in this line...

$sql = "select column_name from information_schema.columns where table_name = 'dbo.myTable'";

change to:

$sql = "select column_name from information_schema.columns where table_name = 'myTable'";

answered Feb 2, 2011 at 18:41

John K.

5,4741 gold badge23 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Conrad Frix · Accepted Answer · 2011-02-02 18:41:49Z

0

Your where statement looks wrong it shouldn't be where table_name = dbo.myTable

it should be

where 
    table_name = 'myTable' 
     and TABLE_SCHEMA = 'dbo'

answered Feb 2, 2011 at 18:41

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Comments

Ken Downs · Accepted Answer · 2011-02-02 18:42:00Z

0

One simple thing: Take that "dbo." out of the table name. That value will be found in the column "table_schema". So it should be "...where table_name='myTablel'"

Assuming all of your drivers are installed, and that you can get results from other queries, that would do it.

answered Feb 2, 2011 at 18:42

Ken Downs

4,8671 gold badge25 silver badges20 bronze badges

Collectives™ on Stack Overflow

Select Column Names from SQL Server using PDO

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related