Column names - 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

General SQL Server Forums

New to SQL Server Programming

Column names

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-04-25 : 13:51:38

I need to pre-map my next conversion in an excel sheet for the customer. What i would like to be able to do is take the column names in the relevant tables in our db and the column names in the corresponsding tables on the client's db and place those values as rows, adjacent to each other, (say, column A is our, column C is theirs. I can pull their column names easy enough by doing a "select * from" and copying the resultset with headers into a new excel sheet. I then delete the data from the excel sheet and leave the column names in place, do a pivot on the table in excel, and i have that part. The problem with the tables on our side is that they are empty. Therefore when i do a "select * from" on our tables, i get empty resultssets, but the resultset shows the column names. I cannot, however, do a "copy with headers" function on that resultset. Does anyone know of a quick and easy way to grab that data from an empty table so i can paste it in excel?

thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-25 : 14:01:23

run

sp_help YourTableName

and see what you get

No, you're never too old to Yak'n'Roll if you're too young to die.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-04-25 : 14:05:14

i get a very cool list of column names in a single column all ready to be dropped into excel. thanks a ton.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-25 : 14:08:12

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-04-25 : 14:17:23

ok, but wait, this now gets a little more complex. On the client's db, i'm using the following script to pull the data i need from multiple tables:


SELECT 	* 
					FROM	PropertyReports pr, PropertyReportItems pri, CombinedDesc cd, CFSData cfs, CombinedDesc cf 
					WHERE	pr.ID = pri.PropertyReportID
					AND		pri.ClassID = cd.ID 
					AND		pr.CFSID = cfs.CFSID
					AND		cfs.CallTypeID = cf.ID
					
					ORDER BY pr.CFSID DESC

Because that data is coming from multiple tables, i can't use the sp_help command to get the same layout. So, when i try to use a "Select * into" command to dump the resultset of this query into a new table:


SELECT 	* INTO dbo.OffenseProperty
					FROM	PropertyReports pr, PropertyReportItems pri, CombinedDesc cd, CFSData cfs, CombinedDesc cf 
					WHERE	pr.ID = pri.PropertyReportID
					AND		pri.ClassID = cd.ID 
					AND		pr.CFSID = cfs.CFSID
					AND		cfs.CallTypeID = cf.ID
					
					ORDER BY pr.CFSID DESC

i run into problems because of the use of the ID column from two different tables (error says ID is already used in the new table, and not unique). What is my workaround to get this query dumped into a new table (i have other reasons for wanting to move that resultset into it's own table).

thanks again

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-25 : 14:21:28

Your workaround is to not use * and write a column list instead and using aliases to avoid duplicate column names because they are not possible in a table.

No, you're never too old to Yak'n'Roll if you're too young to die.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-04-25 : 14:23:07

wait a minute. disregard the previous for now. I see where i might need to use a union to get rid of the duplicate columns in my resultset.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-04-25 : 14:24:18

i see what you mean. thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-25 : 14:28:45

There's also:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

There's a lot of extra data in there you can remove if you don't need it.

And if you really want to mike life simple, you can tag your tables and columns with extended properties to indicate which external table/column they map to. You can then query them using sys.extended_properties.

amitji.tspl
Starting Member

1 Post

Posted - 2012-04-27 : 14:12:44

i am join 2 Excel file threw MS access and i need to file name in last column and each cell

Amit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources