I have a select query(With joins on other tables), and want to export data types of columns to Excel or text. If I have table I used the "Generate script task". But in case of "select query" it is not suitable. I know that the simple solution is to make table of this query, but i have only read-only access to this database. How to do this without make-table ?
Asked
Active
Viewed 1,068 times
2
-
Check this http://stackoverflow.com/questions/7389637/export-sql-query-data-to-excel – Nikhil Vartak Oct 05 '15 at 07:43
-
in the link - Export SQL query data to Excel. But my question is about data types – Vitaly Ascheulov Oct 05 '15 at 08:52
1 Answers
1
Try this
SELECT *
INTO #t
FROM [Your query]
DECLARE @sql VARCHAR(8000)
SELECT @sql = 'bcp "
SELECT t.name as ''Data Type''
FROM tempdb.sys.columns c join sys.types t on c.system_type_id=t.system_type_id
WHERE [object_id] = OBJECT_ID(N''tempdb..#t''); " ' + 'queryout [Your Location]\[File Name].csv -c -t, -Usa -P[password] -S' + @@servername
EXEC master..xp_cmdshell @sql
DROP TABLE #t
Crabster
- 135
- 11
-
Interesting method. You use temp table #t. But I have only read-only access in this base. – Vitaly Ascheulov Oct 05 '15 at 08:31
-
@ВиталийАщеулов that should not be a problem. Did you run the script ? what errors did you get ? – Crabster Oct 05 '15 at 08:48
-
As long as a user can connect to a SQL Server instance, the user should be able to create temporary tables. The solution should not have any problem. – Abhishek Oct 05 '15 at 08:55