Sql Server: How to list all the database names

There are two ways of listing the available database names in SQL Server, using T-SQL

select name from master..sysdatabases

EXEC sp_msForEachDB 'PRINT ''?''';

SQL Server 2008: Ole Automation

Ole Automation in SQL Server 2008 by default is turned off for security reasons. When OLE Automation Procedures are enabled, a call to sp_OACreate will start the OLE shared execution environment.

Executing the following script will enable Ole Automation

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

SQL Server: Data dictionary

Data dictionary creator [1] is a simple-to-use free tool that helped me immensely with a SQL Server database documentation. It stores all the information in Extended Properties, so it’s easier to keep the documentation in sync with the database as it changes. I was able to generate a data dictionary with a couple of of mouse clicks after I have entered the connection string. The dictionary can be exported to various formats: Excel, XML, Word and HTML. Click on the reference [1] for a step by step tutorial on the usage of this utility. (The current version does not seem to support SQL Server 2008). This can be download from codeplex.

Reference:
1. Data Dictionary Creator

Sql Server: How to find the current version?

One of the following two SQL queries could be used to determine the current version of the Microsoft SQL Server [2] that you are working on. I like the first method, for the simplicity of its usage.

SELECT @@version

--OUTPUT
--Microsoft SQL Server 2005 - 9.00.XXXX.00
--Nov XX 200X 16:17:31
--Copyright (c) 1988-2005 Microsoft Corporation
--XXXXXX Edition on Windows NT 5.2 (Build XXXX: Service Pack X)

-------
--OR--
-------

SELECT SERVERPROPERTY('productversion') productversion,
 SERVERPROPERTY ('productlevel') Product Level,
 SERVERPROPERTY ('edition')

--OUTPUT
--Product Version Product Level  Edition
----------------- -------------- --------------
--9.00.XXXX.00    SPX            XXXXX Edition

Following are the product versions and their corresponding names.

8.0 stands for SQL Server 2000
9.0 stands for SQL Server 2005
10.0 stands for SQL Server 2008

Tip: You can run select @@servername in management studio query editor to find the SQL Server name.

References:

1. Microsoft support
2. Microsoft SQL Server

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

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$]')

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:
1. Server Settings Configuration Options