Monday, January 04, 2010

SQL Server Helper & a helper script to redirect SQL to Excel


Useful site for lots of SQL Script examples.

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

SQL Server Helper

If you have Powershell installed, exporting to Excel is probably a non-issue for you.

However, here is a quick script that was useful to me and was fairly simple to implement.

1. Create a directory and copy the Excel shortcut to it.  Rename the shortcut to Excel.lnk
2. Create a command script countalltables.cmd

osql –S [sqlservername] -d [databasename] -E -I -s "," -Q "spcCountAllTables" > CountallTables.Csv
Excel countAllTables.Csv

3. Create a stored procedure in the database spcCountAllTables

create proc spcCountAllTables
set nocount on
SELECT  'myTable' as TableName, COUNT(*) as Total FROM MyTable UNION ALL

4. Run the proc countalltables.cmd

You should see a list of tables you defined with their counts.

No comments: