Pradeesh Thoughts: October 2009

Friday, October 30, 2009

Code Samples - Analysis

Excel Add-ons for Analysis ...

Microsoft Office Excel Add-in for SQL Server Analysis Serviceshttp://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx
XLCubed http://www.xlcubed.com
IntelligentApps http://www.intelligentapps.com
MIS Plain http://www.misag.com
BIXL http://www.bixl.com
QBEX http://www.advanceinfo.com/qbex
Visual OLAP http://www.visualolap.com
O2OLAP http://www.o2olap.com
OLAP NaviGo http://www.olapnavigo.com
Panorama NovaView Excel http://www.panoramasoftware.com
CALUMO - For Excel http://www.calumo.com

Stand-Alone OLAP clients

Panorama NovaView BI Platform http://www.panoramasoftware.com
Proclarity http://www.proclarity.com
TemTec Executive Viewer http://www.temtec.com
Cognos PowerPlay - http://www.cognos.com
BusinessObjects - http://www.businessobjects.com
Crystal Analysis and Reports - http://www.businessobjects.com
MIS onVision - http://www.misag.com
Arcplan inSight and dynaSight http://www.arcplan.com
SoftPro Manager - http://www.softpro.hr
SPSS Olap Hub Information Intelligence Platform - http://www.spss.com/olap_hub
Targit Analysis - http://www.targit.com
Databeacon - http://www.databeacon.com/
Visual Spreadsheet - http://www.tableausoftware.com/
Q4bis - http://www.q4bis.com/
Hummingbird - http://www.hummingbird.com/products/bi/index.html
Vista - http://www.beyond2020.com/products/Vista.html
DeltaMaster - http://www.bissantz.de/index_e.html
Strategy Analyzer - http://www.strategycompanion.com
USoft - http://www.u-soft.com.cn

Microsoft Software for Analysis ( Mosha.com)

Tuesday, October 27, 2009

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Thursday, October 22, 2009

Alter Columns.

SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' ALTER
COLUMN ' + COLUMN_NAME + ' nVARCHAR(' + CAST(CHARACTER_MAXIMUM_LENGTH
AS VARCHAR(10)) + ')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'VARCHAR'

Wednesday, October 21, 2009

GetAllTableSizes

CREATE PROCEDURE GetAllTableSizesAS/* Obtains spaced used data for ALL user tables in the database*/DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listingDECLARE tableCursor CURSORFOR select [name]from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1FOR READ ONLY
--A procedure level temp table to store the resultsCREATE TABLE #TempTable( tableName varchar(100), numberofRows varchar(100), reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50))
--Open the cursorOPEN tableCursor
--Get the first table name from the cursorFETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetchWHILE (@@Fetch_Status >= 0)BEGIN --Dump the results of the sp_spaceused query to the temp table INSERT #TempTable EXEC sp_spaceused @TableName
--Get the next table name FETCH NEXT FROM tableCursor INTO @TableNameEND
--Get rid of the cursorCLOSE tableCursorDEALLOCATE tableCursor
--Select all records so we can use the reultsSELECT * FROM #TempTable
--Final cleanup!DROP TABLE #TempTable
GO

Disk Sizing Easy Way from Microsoft.

http://msdn.microsoft.com/en-us/library/ms178085.aspx

Check Table Spaces...............

create table DataAdmin..tblTableSizes(TableEntryID int identity(1,1) primary key,EntryDate smalldatetime not null default getdate(),TableName varchar(50),Row_Count int,ReservedSize varchar(20),DataSize varchar(20),IndexSize varchar(20),UnusedSize varchar(20),CreationDate smalldatetime )

set quoted_identifier off
GO
Use AdventureWorks2000declare @tablename varchar(50), @statement nvarchar(300), @creationdate smalldatetime
declare csrTables cursor for select top 100 percent [name] from AdventureWorks2000..sysobjects (nolock) where xtype = 'U' and left(name, 3) != 'dt_' open csrTablesfetch next from csrTables into @tablenamewhile @@fetch_status = 0beginset @statement = "insert DataAdmin..tblTableSizes(TableName, Row_Count, ReservedSize, DataSize, IndexSize, UnusedSize) exec sp_spaceused '" + @tablename + "'"set @CreationDate = (select crdate from AdventureWorks2000..sysobjects (nolock) where [name] = @tablename)exec sp_executesql @statementupdate DataAdmin..tblTableSizes set CreationDate = @CreationDate where TableName = @TableNamefetch next from csrTables into @tablenameend
close csrTablesdeallocate csrTables
set quoted_identifier onGO

Monday, October 12, 2009

Find lock on MY Table

select convert (smallint, req_spid) As spid, rsc_dbid As dbid, object_name(rsc_objid) As ObjectName, rsc_indid As IndId, substring (v.name, 1, 4) As Type, substring (rsc_text, 1, 32) as Resource, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR' and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS' and master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L' and object_name(rsc_objid) = 'My Table ' order by spid

Friday, October 9, 2009

Find Reporting Services DATA

--Find the procedure names that are being used along with report, datasource names
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
ORDER BY name
--Find the procedure names that are used in reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
distinct x.value('CommandText[1]','VARCHAR(50)') AS CommandText
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
--Get all text adhoc sql in the reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type != 3
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') is null --= 'CommandText'
ORDER BY name--Find the distinct reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
distinct name
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
ORDER BY name

--Get the data sources
select Path, Name from dbo.Catalog
where Type = 5
--Get the report names and their paths
select Path, Name from dbo.Catalog
where Type = 2
--Get the supporting docs used in the reports
select Path, Name from dbo.Catalog
where Type = 3
--Is there a cache policy to expire after n minutes?
select Path, Name, ExpirationFlags, CacheExpiration
from dbo.Catalog c join dbo.CachePolicy cp
on c.ItemID = cp.reportID
where ExpirationFlags = 1
--Is there a cache policy to expire on a report specific/shared schedule?
select Path, Name, ExpirationFlags, CacheExpiration
from dbo.Catalog c join dbo.CachePolicy cp
on c.ItemID = cp.reportID
where ExpirationFlags = 2
--Which users are using these reports
select UserName from dbo.Users
where UserName not in
('Everyone', 'NT AUTHORITY\SYSTEM', 'NT AUTHORITY\NETWORK SERVICE',
'BUILTIN\Administrators', 'Domain Users')
--find the favorite rendering types
select format, count(1) as cnt
from dbo.ExecutionLog
group by format
order by cnt
--Get the most frequently used reports
select c.Path, c.Name, count(1) as cnt
from dbo.ExecutionLog e join dbo.Catalog c
on e.ReportID = c.ItemID
group by c.Path, c.Name
order by cnt desc
--Get the reports that take longer to retrieve the data
select c.Path, c.Name, max(TimeDataRetrieval)/1000. as seconds
from dbo.ExecutionLog e join dbo.Catalog c
on e.ReportID = c.ItemID
group by c.Path, c.Name
order by seconds desc
--Get the most frequent users
select UserName, count(1) as cnt from dbo.ExecutionLog
group by UserName
order by cnt desc
--Get the latest subscription reports delivered
select c.Path, c.Name, LastRunTime, replace(LastStatus, 'Mail sent to ','') as lastStatus
from dbo.Subscriptions s join dbo.Catalog c
on s.Report_OID = c.ItemID
order by LastRunTime desc
--get users and thier roles
select UserName, RoleName, Description
from dbo.Roles r join dbo.PolicyUserRole pur
on r.roleid = pur.roleid
join dbo.Users u
on pur.userid = u.userid
where UserName not in ('BUILTIN\Administrators', 'Domain Users', 'IUSR_REPORT02')
order by UserName

Encrpt all Stored Procedures.

CREATE FUNCTION [dbo].[fncGetAsIndex](@searchText nvarchar(max))RETURNS intASBEGIN
DECLARE @asIndex_current int,@asIndex_total int declare @preChar char(1),@postChar char(1)
set @asIndex_current = 0 ; set @asIndex_total = 0
while (charindex ('as',lower(@searchText)) > 0 )
begin
set @asIndex_current = charindex ('as',lower(@searchText)) set @asIndex_total = @asIndex_total + @asIndex_current + case when @asIndex_total=0 then 0 else len('as')-1 end
set @preChar = substring(@searchText,@asIndex_current - 1 , 1) set @postChar = substring(@searchText,@asIndex_current + len('as') ,1) if (@preChar = '' or @preChar = ')' or @preChar = char(10) or @preChar = char(9))
begin
if (@preChar = '' and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = ')' and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = char(10) and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = char(9) and ( @postChar = '' or @postChar = char(13)) )
return @asIndex_total
end
set @searchText = substring(@searchText,@asIndex_current + len('as') ,len(@searchText) - @asIndex_current)
end
RETURN 0
END

----------------------------------------------------

CREATE FUNCTION [dbo].[fncGetCreateIndex](@type varchar(2),@searchText nvarchar(max))RETURNS intASBEGIN
declare @typeStatement varchar(10)
if @type = 'P' set @typeStatement = 'PROC' else set @typeStatement = 'FUNCTION' DECLARE @createIndex_current int,@createIndex_total int set @createIndex_current = 0 ; set @createIndex_total = 0
while (charindex ('create',lower(@searchText)) > 0 )
begin
set @createIndex_current = charindex ('create',lower(@searchText)) set @createIndex_total = @createIndex_total + @createIndex_current + case when @createIndex_total=0 then 0 else len('create')-1 end
if (lower(left(ltrim(rtrim(substring(@searchText,@createIndex_current + len('create'),len(@searchText)))),len(@typeStatement))) = lower(@typeStatement))
return @createIndex_total
set @searchText = substring(@searchText,@createIndex_current + len('create') ,len(@searchText) - @createIndex_current) end
RETURN 0
END

------------------------------------------------------------------------

CREATE proc [dbo].[NwdicEncrypt]@type varchar(2),@physicalTableName nvarchar(100)
as
begin
IF OBJECT_ID('tempdb..#backup') IS NOT NULL DROP TABLE #backup
CREATE TABLE #backup ( id BIGINT IDENTITY(1, 1), spname NVARCHAR(100) NOT NULL, sptext NVARCHAR(MAX) NOT NULL, encrypttext NVARCHAR(MAX) NULL, encryptstatus BIT NOT NULL DEFAULT ( 0 ), errMessage NVARCHAR(max) NULL )
DECLARE @sptexttable TABLE ( id BIGINT IDENTITY(1, 1), spname NVARCHAR(100), sptext NVARCHAR(MAX) )
INSERT INTO @sptexttable (spname , sptext) select [name], object_definition(object_id) as [text] from sys.objects where [type]=@type and object_definition(object_id) is not null and [name] not in ('NwdicEncrypt','fncGetAsIndex')
DECLARE @sptext NVARCHAR(MAX), @spname NVARCHAR(max)DECLARE @errMessage NVARCHAR(MAX)DECLARE @counter BIGINT
SET @counter = 1
WHILE (@counter <= ( SELECT MAX(id) FROM @sptexttable )) BEGIN
INSERT INTO #backup ( spname , sptext) SELECT spname, sptext FROM @sptexttable WHERE id = @counter
DECLARE @tempproc NVARCHAR(MAX) DECLARE @procindex INT DECLARE @newProc NVARCHAR(MAX) DECLARE @newheader NVARCHAR(MAX) DECLARE @header NVARCHAR(MAX) DECLARE @asindex INT DECLARE @replacetext NVARCHAR(MAX)
select @tempproc=sptext FROM @sptexttable WHERE id = @counter
BEGIN TRY
set @procindex = dbo.[fncGetCreateIndex](@type,@tempproc)
set @asindex = dbo.fncGetAsIndex(@tempproc)
select @spname = spname FROM @sptexttable WHERE id = @counter
set @header = SUBSTRING(@tempproc, @procindex, @asindex - @procindex)
set @newheader = 'ALTER ' + SUBSTRING(@header,LEN('create') + 1 ,len(@header) ) set @newheader = STUFF(@newheader, len(@newheader), 1, CHAR(13) + 'WITH ENCRYPTION' + CHAR(13))
set @newProc = REPLACE(@tempproc, @header, @newheader)
BEGIN TRY
begin EXEC ( @newProc ) UPDATE #backup SET encrypttext = @newProc, encryptstatus = 1 WHERE id = @counter end
END TRY BEGIN CATCH
begin set @errMessage = error_message() UPDATE #backup SET encrypttext = @newProc, encryptstatus = 0 , errMessage = @errMessage WHERE id = @counter end
END CATCH
END TRY BEGIN CATCH
begin set @errMessage = error_message() UPDATE #backup SET encrypttext = @newProc, encryptstatus = 0 , errMessage = @errMessage WHERE id = @counter end
END CATCH
SET @counter = @counter + 1
END exec ('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @physicalTableName + ']'') AND type in (N''U''))DROP TABLE [dbo].[' + @physicalTableName + ']
create table ' + @physicalTableName + ' ( id BIGINT , spname NVARCHAR(100) NOT NULL, sptext NVARCHAR(MAX) NOT NULL, encrypttext NVARCHAR(MAX) NULL, encryptstatus BIT NOT NULL DEFAULT ( 0 ), errMessage NVARCHAR(max) NULL ) insert into ' + @physicalTableName + ' select * from #backup'
)
end

Pradeesh Thoughts