BGINFO - display confirgutaion properties on desktop

 http://technet.microsoft.com/en-us/sysinternals/bb897557.aspx

Vista and Windows Server 2008 the "All Users" folder

Vista and Windows Server 2008 the "All Users" folder is at the following path

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Startup

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