Updating Columns. - SQL Server Forums

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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Updating Columns.

Author

Topic

potchakri
Starting Member

10 Posts

Posted - 2007-09-21 : 09:08:37

Hi All,

I am facing a problem to update the columns.

The issue is ..I am having 340 tables in SQL database with a total of 8500 columns.
The descriptions for all the columns in an excel sheet.

Is there any means by which we can run a query that which loads the descriptions to all the columns instead of doing that manually?

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 09:14:23

Load the excel spreadsheet into a Staging Table in your database, and then you should be able to construct some dynamic SQL from there.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 09:21:20

This would help you in loading data from EXCEL to table
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

potchakri
Starting Member

10 Posts

Posted - 2007-09-21 : 09:49:17

Hi Kristen and Madhivanan,

Thank you very much for the replies but its not regarding loading data into the columns, its regarding giving column descriptions for every column in the tables.I need to get if there is any procedure to automate the process of giving descriptions to the columns.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 09:58:05

Krsiten pointed out that. Read his reply again

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 11:15:32

"its regarding giving column descriptions for every column in the tables"

If you give us an example of some data we can give you an example to point you in the right direction.

Otherwise we are just guessing, and will probably waste our time because the answers won;t fit your problem.

Kristen

potchakri
Starting Member

10 Posts

Posted - 2007-09-21 : 23:51:08

Hi Kristen,
Thanks for the quick replies.

Coming to the example,

The scenerio is we created tables in the database diagrams menu under the database. Now going to the custom view of the table, and selecting the Column name and the Description options, the table gets displayed with the columns and the description menus.

My question is that do we have any procedure/dynamic SQL query for updating all the description fields for the respective columns.

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 02:26:12

INFORMATION_SCHEMA.COLUMNS probably has the data you are referring to.

But I still don't get what you want to do, and I don't see any example data either!

Kristen

potchakri
Starting Member

10 Posts

Posted - 2007-09-22 : 03:02:31

Hi Kirsten,
I found the procedure for the same.

pls go through...

-- Displaying the Column Descriptions

SELECT
[Table Name] = i_s.TABLE_NAME,
[Column Name] = i_s.COLUMN_NAME,
[Description] = s.value
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
sys.extended_properties s
ON
s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.minor_id = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
-- AND i_s.TABLE_NAME = 'table_name'
ORDER BY
i_s.TABLE_NAME, i_s.ORDINAL_POSITION

-- Syntax for Adding the Column Descriptions

EXEC sp_addextendedproperty
'MS_Description',
'some description',
'user',
dbo,
'table',
table_name,
'column',
column_name

Thank you a lot for the replies Kirsten.

Its a nice time interacting with you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources