List all the columns in the all the tables in all the databases

List all the columns in all the databases.


/*

CREATE TABLE #TmpColumns(
[TABLE_CATALOG] [nvarchar](128) NULL,
[TABLE_SCHEMA] [nvarchar](128) NULL,
[TABLE_NAME] [sysname] NOT NULL,
[COLUMN_NAME] [sysname] NULL,
[ORDINAL_POSITION] [int] NULL,
[COLUMN_DEFAULT] [nvarchar](4000) NULL,
[IS_NULLABLE] [varchar](3) NULL,
[DATA_TYPE] [nvarchar](128) NULL,
[CHARACTER_MAXIMUM_LENGTH] [int] NULL,
[CHARACTER_OCTET_LENGTH] [int] NULL,
[NUMERIC_PRECISION] [tinyint] NULL,
[NUMERIC_PRECISION_RADIX] [smallint] NULL,
[NUMERIC_SCALE] [int] NULL,
[DATETIME_PRECISION] [smallint] NULL,
[CHARACTER_SET_CATALOG] [sysname] NULL,
[CHARACTER_SET_SCHEMA] [sysname] NULL,
[CHARACTER_SET_NAME] [sysname] NULL,
[COLLATION_CATALOG] [sysname] NULL,
[COLLATION_SCHEMA] [sysname] NULL,
[COLLATION_NAME] [sysname] NULL,
[DOMAIN_CATALOG] [sysname] NULL,
[DOMAIN_SCHEMA] [sysname] NULL,
[DOMAIN_NAME] [sysname] NULL
) ON [PRIMARY]

insert into #TmpColumns
exec dbo.uspSearchAllColumnNames
@Database = null,
@Schema = null,
@Table = null,
@Column = '%office%'

select
*
from
#TmpColumns
*/

CREATE PROC [dbo].[uspSearchAllColumnNames](
@Database sysname = null,
@Schema sysname = null,
@Table sysname = null,
@Column sysname = null
)
as

DECLARE @Template NVARCHAR(2000)
DECLARE @Sql NVARCHAR(2000)
DECLARE @Catalog sysname
DECLARE @RowId int
DECLARE @MaxCnt int
SELECT
*
INTO #Columns
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
1=0
SET @Template = '
INSERT INTO #Columns
SELECT
*
FROM
[<>].INFORMATION_SCHEMA.COLUMNS
'
SELECT
IDENTITY(INT,1,1) AS RowID,
[Name]
INTO #DBNames
FROM
sys.databases
WHERE
owner_sid <> 0x01
SELECT
@RowId = 1,
@MaxCnt = COUNT(*)
FROM
#DBNames
WHILE (@RowId <= @MaxCnt)
BEGIN
SELECT
@Catalog = [Name]
FROM
#DBNames
WHERE
@RowId = RowId
SET @Sql = REPLACE(@Template, '<>', @Catalog)
EXEC sp_executesql @Sql
SET @RowId = @RowId +1
end


/*
--debugging
declare
@Database sysname,
@Schema sysname,
@Table sysname,
@Column sysname
SET @Column = '%vista%'
*/
SELECT
*
FROM
#Columns
where
(@Database IS NULL OR TABLE_CATALOG LIKE @Database)
AND (@Schema IS NULL OR TABLE_SCHEMA LIKE @Schema)
AND (@Table IS NULL OR TABLE_NAME LIKE @Table)
AND (@Column IS NULL OR COLUMN_NAME LIKE @Column)
ORDER BY
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME


DROP TABLE #Columns
DROP TABLE #DBNames

Finding the largest table in a database

A good article with SQL script for calculating the largest SQL tables inside a database including the size occupied by the indexes:
http://www.sqlteam.com/article/finding-the-biggest-tables-in-a-database



/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@sqlteam.com
* v1.1
*
**************************************************************************************/
declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables

Search for a text string in a database's T-SQL

I believe the original author is Scott Burnell.


declare @textToSearchFor nvarchar(200)
declare @objectTypeToSearch char(1)
set @textToSearchFor = 'dimManagerCode'
-- use this as a sproc parameter
set @objectTypeToSearch = 'a'
-- use this as a sproc parameter (t=table,p=procedure,v=views,f=functions,a=all)
-- procedures
if (@objectTypeToSearch in ('a', 'p'))
begin
select
isnull('[' + s.[name] + '].[', '[') + p.[name] + ']' as ProcName --objectName.'
from
sys.procedures p
inner join sys.sql_modules m
on p.object_id = m.object_id
left join sys.all_objects obj
on p.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
m.definition like '%' + @textToSearchFor + '%'
order by
ProcName
end
-- tables
if (@objectTypeToSearch in ('a', 't'))
begin
select
'[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' as TableName --objectName
from
INFORMATION_SCHEMA.COLUMNS c
where
c.COLUMN_NAME like '%' + @textToSearchFor + '%'
order by
TableName
end
-- views
if (@objectTypeToSearch in ('a', 'v'))
begin
select
isnull('[' + s.[name] + '].[', '[') + p.[name] + ']' as ViewNameDefinition --objectName
from
sys.views p
inner join sys.sql_modules m
on p.object_id = m.object_id
left join sys.all_objects obj
on p.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
m.definition like N'%' + @textToSearchFor + N'%'
select
isnull('[' + s.[name] + '].[', '[') + obj.[name] + ']' as ViewNameOutputColumn --objectName
from
sys.all_columns c
inner join sys.objects obj
on c.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
obj.[type] = 'v'
and c.[name] like '%' + @textToSearchFor + '%'
select distinct
'[' + VIEW_SCHEMA + '].[' + VIEW_NAME + ']' as ViewName,
'[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '].[' + COLUMN_NAME + ']'as UsesTableColumn
from
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
where
COLUMN_NAME like '%' + @textToSearchFor + '%'
order by
ViewName
end
-- functions
if (@objectTypeToSearch in ('a', 'f'))
begin
select
isnull('[' + s.[name] + '].[', '[') + obj.[name] + ']' as FunctionName --objectName
from
sys.objects obj
inner join sys.sql_modules m
on obj.object_id = m.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
obj.[type] = 'FN'
and m.definition like '%' + @textToSearchFor + '%'
order by
FunctionName
end

Stored procedure for truncating SQL 2005 Logs and shrinking the database

create proc [dbo].[uspShrinkDatabase]
as
DECLARE @LogFileName sysname
DECLARE @DataFileName sysname
DECLARE @CatalogName sysname
set @CatalogName = db_name()
SELECT
@LogFileName = rtrim([name])
from
sys.database_files
where
type_desc = 'LOG'
SELECT
@DataFileName = rtrim([name])
from
sys.database_files
where
type_desc = 'rows'
select
@LogFileName as LogFileName,
@DataFileName as DataFileName
Checkpoint;
Backup LOG @CatalogName with Truncate_Only;
DBCC SHRINKFILE(@LogFileName, 0, TRUNCATEONLY);
DBCC SHRINKDATABASE(@CatalogName);

SQL split function by Karen Gayda

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[fnSplit]
( @vcDelimitedString varchar(8000),
@vcDelimiter varchar(100) )
RETURNS @tblArray TABLE
(
ElementID smallint IDENTITY(1,1), --Array index
Element varchar(1000) --Array element contents
)
AS
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data

PARAMETERS:
@vcDelimitedString - The string to be split
@vcDelimiter - String containing the delimiter where
delimited string should be split

RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string

USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID

AUTHOR: Karen Gayda

DATE: 05/31/2001

MODIFICATION HISTORY:
WHO DATE DESCRIPTION
--- ---------- ---------------------------------------------------

***************************************************************************/
BEGIN

DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint


SET @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END

RETURN
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

Dynamic Sort Order In T-SQL

create proc GetEmployees
@SortField nvarchar(20)
as

select * from Employees
order by
case @SortField
when 'FirstName' then cast (FirstName as sql_variant)
when 'LastName' then cast (LastName as sql_variant)
when 'HireDate' then cast (HireDate as sql_variant)
else cast (EmployeeID as sql_variant)
end

Very clever. Originally found at http://www.angrycoder.com/article.aspx?ArticleID=131 "Dynamic Sort Order In T-SQL"

A comment states:
There are two problems (at least) with the code. First, it will not use indexes and thus be fairly slow. And second, it will sort numerical columns alphabeticaly. That is 1, 11, 2, 3, 35, 4, ... Usually not exactly what you wanted.While you might solve the second problem by prepending enough zeroes or spaces or something it would only make the first problem worse. It's SLOW!Sorry, there is no nice solution. There can't really be as the order column can totally change the optimal execution plan. And you can't (so far) have several execution plans for a single query. So ... it's anoying, but the only performance effective solution is to repeat the query as many times as you have the different orders and choose the right one by a row of IF statements :-(

Reading an Excel 2007 file from SSIS 2005

Since SSIS uses ODBC to read Excel 2007 files, changing the ODBC connection string to the following:

Data Source=D:\Test.xlsx;Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 8.0;

Data Source=D:\Test.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0


To read an xslm file, I think you need Extended Properties="Excel 12.0
Macro". Similarly, to read an xslx file, you will need "Excel 12.0 Xml".

See the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\ISAM Formats\ for the various providors.

Error 1004 when setting the VisibleItemsList in Excel 2007

If you are receiving the error 1004 while setting the filters for a pivot table in Excel 2007, you need to add "pivfld.CubeField.EnableMultiplePageItems = True" this line to your code.

dim rgstrMonthNames() as variant
'Dynamically build string array from a list....
rgstrMonthNames() = Array("Jan 2007", "Jan 2006")

For Each ws In Worksheets
For Each pt In ws.PivotTables
Set pivfld = pt.PivotFields("[Dim Date].[MonthNames].[MonthNames]") pivfld.CubeField.ClearManualFilter
pivfld.CubeField.EnableMultiplePageItems = True
pivfld.VisibleItemsList = rgstrMonthNames()
End If
Next
Next

ActiveWorkbook.RefreshAll

Setting chart title in Excel 2007

Worksheets(0).ChartObjects(0).Chart.ChartTitle.Text = "new title"

Size and row count of tables in a SQL server

The code creates a temp tables with the total number of rows and size in KB for all the tables in the current SQL server catalog.


declare @TableName nvarchar(1000)

begin try
drop table #TblSpace
end try
begin catch
end catch

create table #TblSpace
(
[name] nvarchar(1000),
[rows] bigint,
reserved nvarchar(1000),
data nvarchar(1000),
index_size nvarchar(1000),
unused nvarchar(1000)
)

declare cur cursor for
select
cast('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' as nvarchar(1000)) as TableName
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'

open cur

fetch next from cur into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

insert into #TblSpace
EXEC sp_spaceused @TableName

fetch next from cur into @TableName
end

close cur
deallocate cur

select * from #TblSpace order by [rows] desc

Using SQL to iterate through all the columns in a table

I use this code in ETL projects where almost all of the columns need to be cleaned, and it is easier to execte the same SQL code over all the columns in all the imported tables, rather than write custom code for each column in the ETL package (SSIS in my case).

declare @TableName as varchar(2000)
declare @ColName as varchar(2000)
declare @Sql as nvarchar(4000)

declare curColNames cursor for
select distinct
'[' + C.TABLE_SCHEMA + '].[' + C.TABLE_NAME + ']' as TableName
,'[' + C.COLUMN_NAME + ']' as ColName
from
INFORMATION_SCHEMA.COLUMNS C
join INFORMATION_SCHEMA.TABLES T
on C.TABLE_SCHEMA = T.TABLE_SCHEMA
and C.TABLE_NAME = T.TABLE_NAME
where
T.TABLE_TYPE = 'BASE TABLE'
order by
TableName
,ColName


open curColNames

fetch next from curColNames
into @TableName, @ColName

WHILE @@FETCH_STATUS = 0
BEGIN
set @Sql = 'update ' + @TableName + ' set ' + @ColName + '= ltrim(rtrim(' + @ColName + '))'

exec sp_executesql @Sql

fetch next from curColNames
into @TableName, @ColName
end

close curColNames
deallocate curColNames

Macro for setting a pivot table filter using Excel 2007

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Geography WW Physical].[Physical Sales Location Name].[Physical Sales Location Name]").ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Geography WW Physical].[Physical Sales Location Name].[Physical Sales Location Name]").CurrentPageName = "[Dim Geography WW Physical].[Physical Sales Location Name].&[United States]"

Clearing a SSAS cube cache

Snippet from:
http://geekswithblogs.net/darrengosbell/archive/2007/08/30/SSAS-Query-Performance-Tuning-Whitepaper.aspx

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ClearCache>
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>
</Batch>

GO

SELECT {} ON 0 FROM [Adventure Works]

Disabling alerts in Excel

Disabling alerts in Excel when deleting worksheets in macros.

Application.DisplayAlerts = False
Worksheets("Lists").Delete
Application.DisplayAlerts = True

GUID utilities suitable for use in Excel

I briefly played with this set of utilities, and I'm very pleased with it.

http://www.trigeminal.com/code/guids.bas

Useful article on SQL CLR security

Security in the CLR World Inside SQL Server
http://www.code-magazine.com/article.aspx?quickid=0603031&page=1

Free icons for use in personal and commercial ASP.NET web sites

http://www.aspneticons.com/

Also check out:
http://www.openclipart.org/

"select top 100 percent ... from ... order by ..." not guaranteed to work inside of views

I've recently run across a SQL view attempting to specify the sort order inside a view using "select top 100 percent ... from ... order by ...". Unfortunately it is not guaranteed to work.

See this article for an explanation:
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

HttpRedirection module problems with Reporting Services 2005

I tried using Mike Volodarsky's HttpRedirection module (http://mvolo.com/blogs/serverside/archive/2007/05/24/Redirect-clients-in-your-application-with-HttpRedirection-module.aspx) with the default IIS web site, which includes Reporting Services 2005. The module worked great, but caused an error when I tried to access reporting services. I recommend sticking with IIRF (http://www.codeplex.com/IIRF/), which worked flawlessly for me, and was compatible with Reporting Services 2005.

Definitely check out Mike's site for useful IIS information.

SSIS import problems - imported rows returning NULLs

When importing data from an Excel workbook using SSIS, some values may be returned as NULL. This is caused by the OLEDB driver believing some columns have multiple data types. Use the IMEX flag to work around this problem.

http://support.microsoft.com/kb/189897/EN-US/
http://support.microsoft.com/default.aspx/kb/194124


change this
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data_200708.xls;Extended Properties="EXCEL 8.0;HDR=YES";

to

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data_200708.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

Securely wiping a hard disk

http://dban.sourceforge.net/

Online Bussiness Intelligence Courses

I'm currently enrolled in this program, and I recommend it for those interested in Bussiness Intelligence:
http://bellevuecollege.edu/catalog/degrees/busit/

Loading and Running a Remote Package Programmatically

Loading and Running a Remote Package Programmatically
http://msdn2.microsoft.com/en-us/library/ms403355.aspx

SQL Tools

http://www.apexsql.com
http://www.aquafold.com/index.html
http://www.bestsofttool.com/
http://www.dbsolo.com/schema_comparison.html
http://www.devlib.net/decryptsql.htm
http://www.Embarcadero.com
http://www.orafaq.com/tools/dkg/dbdiff.htm
http://www.red-gate.com/ (there is a trial version that can be downloaded)
http://www.schematodoc.com
http://www.sleepyant.com/index.php
http://www.sqlaccessories.com/
http://www.sqledit.com/dcmp/
http://www.sqlmanager.net/products/postgresql/manager
http://www.synametrics.com/SynametricsWebApp/WinSQLFeatures.jsp
http://www.teratrax.com/sql_compare
http://www.xsqlsoftware.com/LiteEdition.aspx

Redirecting HTTP to HTTPS

I recommend using the IIRF isapi filter for performing HTTP to HTTPS redirects. You can find the project at http://www.codeplex.com/IIRF/. I've successfully used this isapi filter for some of my projects.

The syntax of IIRF is very similar to the mod_rewrite for Apache. Googling for "RewriteRule AND https" retrieved some valuable samples and tutorials:

http://www.whoopis.com/howtos/apache-rewrite.html

http://wiki.apache.org/httpd/RewriteHTTPToHTTPS

http://www.karkomaonline.com/article.php/2005080614195334

I'm sure there are more terse ways to write the regular expressions. However, this should provide a good start.

Make sure the folder exists and set logging to max while debugging.
------- 8< ------- >8 -------
RewriteLog c:\temp\iirfLog.out
RewriteLogLevel 5
------- 8< ------- >8 -------

These are the rules I use on our dev box called "PEER". Some of the apps require the full server and domain name. These sets of rules expand the name from "PEER" to the complete domain "peer.dev.us.company.com". Since I'm not an expert on regex I use the logical OR to concatenate the rules.
------- 8< ------- >8 -------
RewriteCond %{HTTPS} on
#RewriteCond %{SERVER_PORT} ^443$
RewriteCond %{HTTP_HOST} ^peer$ I,OR
RewriteCond %{HTTP_HOST} ^peer\:0-9*$ I,OR
RewriteCond %{HTTP_HOST} ^peer.dev.us.company.com\:0-9*$ I
RewriteRule ^/(.*)$ https://peer.dev.us.company.com/$1 R
------- 8< ------- >8 -------

#tests to see if the connection is already HTTPS
RewriteCond %{HTTPS} on

#since we only have one https site, I ignore this. Useful for more than one site.
#RewriteCond %{SERVER_PORT} ^443$

#was the server typed in as just "PEER".
#I - ignore case
#OR - logical OR with the rule below
RewriteCond %{HTTP_HOST} ^peer$ I,OR

#was the server typed in as "PEER" plus some port?
#I - ignore case
#OR - logical OR with the rule below
RewriteCond %{HTTP_HOST} ^peer\:0-9*$ I,OR

#was the server typed in as "peer.dev.us.company.com" plus some port?
#I - ignore case
#OR - logical OR with the rule below
RewriteCond %{HTTP_HOST} ^peer.dev.us.company.com\:0-9*$ I

#Redirect to the https site with the fully qualified name.
#Keep the path the user specified.
#So if a user types in "http://peer/reports" they are
#redirected to "http://peer.dev.us.company.com/reports"
RewriteRule ^/(.*)$ https://peer.dev.us.company.com/$1 R




This rule redirects all port 80 requests to the https site. I've seen the rule written as "^!443$", which means all connections on ports other than 443 are redirected to the https site. I used port 80, because we have test sites on other ports which don't require SSL.
------- 8< ------- >8 -------
RewriteCond %{SERVER_PORT} ^80$
RewriteRule ^/(.*)$ https://www.company.com/$1 R
------- 8< ------- >8 -------