| Author |
 Topic  |
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
 Posted - 04/25/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 04/25/2012 : 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
USA
614 Posts |
Posted - 04/25/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 04/25/2012 : 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
USA
614 Posts |
Posted - 04/25/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 04/25/2012 : 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
USA
614 Posts |
Posted - 04/25/2012 : 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
USA
614 Posts |
Posted - 04/25/2012 : 14:24:18
|
| i see what you mean. thanks. |
|
|
|
robvolk
Most Valuable Yak
USA
15566 Posts |
Posted - 04/25/2012 : 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
India
1 Posts |
Posted - 04/27/2012 : 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 |
|
|
| |
Topic |
|
Column names
