Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Create Array of Columns

Author  Topic 

larrythek
Starting Member

7 Posts

Posted - 2008-02-18 : 08:58:17
Absolute beginner to SQL here...

I'm try to create an array of columns and then refer to these columns at the asp.net level.

Can't seem to find this in the SQL literature...so I'm probably looking at this all wrong.

Thanks in advance.

Larry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 09:01:47
SQL doesnt have array concept. You could probably use a lengthy varchar field to act as an array which contains column information.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-18 : 09:05:14
You could use the sysobjects and syscolumns tables from within the relevant db to derive all the column names per table

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-18 : 09:08:25
[code]select column_name from information_schema.columns where table_name = 'something'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

larrythek
Starting Member

7 Posts

Posted - 2008-02-18 : 09:10:17
Thanks for the reply.

Can you (or anyone) show me an example of this...or is there another way out of this.

I have multiple columns with similar content. I am looking to see which of these are NULL. If there is no array concept, I would have to explicitly test each of the columns by a unique name.

Thanks for any help here

Larry
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-18 : 09:27:15
Is this?

select column_name from information_schema.columns where table_name = 'something' and is_nullable='YES'


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-18 : 10:01:10
Try:

Create Procedure GetColumnList(@TableName varchar(100), @ColumnList varchar(8000) OUTPUT)
As Begin
Declare @Result varchar(8000)

Select @Result = Coalesce(@Result + ',', '') + SC.name
From sysobjects so
inner join syscolumns sc on so.id = sc.id
Where SO.xtype = 'U'
And SO.name = @TableName
Order By SC.colorder

Set @ColumnList = @Result
End

--Usage
Declare @Result varchar(8000)
Exec GetColumnList 'sysdtslog90', @Result OUTPUT
Print @Result

Then in your ASP.Net page you can do something like (UNTESTED!!!):

Try
' Set up the connection
Dim sqlConn As SqlConnection = New SqlConnection(ConnectionString)
sqlConn.Open()

Dim sqlComm As SqlCommand = New SqlCommand()
sqlComm.Connection = sqlConn
sqlComm.CommandType = CommandType.StoredProcedure

' Set up the command
sqlComm.CommandText = "GetColumnList"
sqlComm.Parameters.AddWithValue("@TableName", "sysdtslog90")
sqlComm.Parameters.Add("@ColumnList", SqlDbType.VarChar, 8000)
sqlComm.Parameters("@ColumnList").Direction = ParameterDirection.Output

' Get the value
sqlComm.ExecuteNonQuery()
Dim arrColumns As String() = sqlComm.Parameters("@ColumnList").Value.ToString().Split(",")

' Close connection
sqlConn.Close()
Catch ex As Exception
' Deal w/ error
End Try
Go to Top of Page
   

- Advertisement -