SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Create Array of Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

larrythek
Starting Member

USA
7 Posts

Posted - 02/18/2008 :  08:58:17  Show Profile  Visit larrythek's Homepage  Click to see larrythek's MSN Messenger address  Reply with Quote
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

Edited by - larrythek on 02/18/2008 09:01:16

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/18/2008 :  09:01:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2004 Posts

Posted - 02/18/2008 :  09:05:14  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 02/18/2008 :  09:08:25  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
select column_name from information_schema.columns where table_name = 'something'


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

larrythek
Starting Member

USA
7 Posts

Posted - 02/18/2008 :  09:10:17  Show Profile  Visit larrythek's Homepage  Click to see larrythek's MSN Messenger address  Reply with Quote
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

India
22744 Posts

Posted - 02/18/2008 :  09:27:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

USA
145 Posts

Posted - 02/18/2008 :  10:01:10  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000