How To Display Only Column Names SQL Query

How To Display Only Column Names SQL Query

I have a scenario where I want to just display the column names and not the data at all.

So I started to dig into my memory where I remember achieving this task using INFORMATION_SCHEMA.COLUMNS

I also started to find some interesting solutions googling a bit. I should agree Bing wasn’t able to get to my results. But I would still recommend Bing as it has some really eye catching features compared to Google. So let’s get back to our job of achieving what we need using SQL query.

Below are three ways through which I was successfully able to achieve this task for a table named “Article”

1) Logical Way – Not sure how effective this is though

SELECT * FROM Article WHERE (1 = 0)

2) Most recommended way to get what exactly you need

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘Article’

3) Another logical way which is pretty interesting
SELECT TOP 0 * FROM Article

So Now I know i don’t have to bing again for getting this task done in future. That’s the reason I am blogging this so that you people can also recommend others to use my blog to get this task done pretty quick.

SQL Server – Import Data From Excel

This article will demonstrate one of the several ways of importing data into a SQL Server table from an Excel spread sheet. I will use a simple SQL query to import data instead of SSIS/DTS. The precursor to achieving the data transfer will be to perform a onetime operation via, enabling the running of Ad Hoc Distributed Queries on your SQL Server database. This can be accomplished by using the SQL Server 2005 Surface Area Configuration Utility or by executing the following code in SQL Server Management Studio’s Query Editor/Query Analyzer:

  
 

sp_configure ’show advanced options’, 1

GO

reconfigure

GO

sp_configure ’Ad Hoc Distributed Queries’, 1

GO

reconfigure

 

sp_configure ‘show advanced options’, 1

GO

reconfigure

GO

sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

reconfigure

When the server is capable of running Ad Hoc Distributed Queries we are all set to run SQL queries to import data from an MS-Excel spreadsheet. It must kept in mind that an Excel file with all the intended columns and data has to exit, before data can be imported (also the file must be closed when data is transferred). In the following code sample, data from the spreadsheet “EMP” in the Excel file, will be imported into the “employee” table in the SQL Server database using the OPENROWSET.

–Export data to Excel

select * into Employee from openrowset(‘Microsoft.Jet.OLEDB.4.0′ ,

‘Excel 8.0;Database=c:\test\test.xls’,'Select * from [EMP$]‘)

 

–Export data to Excel

select * into Employee from openrowset(‘Microsoft.Jet.OLEDB.4.0′ ,

‘Excel 8.0;Database=c:\test\test.xls’,'Select * from [EMP$]‘)

In the near future I will demonstrate how to import multiple spreadsheets into SQL Server database using SSIS, via dynamic discovery of all available spreadsheets in one Excel file.

References:
http://cavemansblog.wordpress.com/2009/07/06/sql-server-import-data-from-excel/
http://msdn.microsoft.com/en-us/library/ms189631.aspx
http://support.microsoft.com/kb/321686