IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.DST_SCHEMAINDEX') AND OBJECTPROPERTY(id, N'IsView') = 1)
	EXEC('CREATE VIEW dbo.DST_SCHEMAINDEX
	AS
	SELECT DISTINCT CONVERT(nvarchar(128),u.name) AS OWNER_NAME
	,CONVERT(nvarchar(128),o.name) AS TABLE_NAME
	,CONVERT(nvarchar(128),i.name) AS CONSTRAINT_NAME
	,CONVERT(nvarchar(128),CASE i.indid 	WHEN 1 THEN ''CLUSTERED''	ELSE ''NONCLUSTERED'' END) AS CLUSTERED_TYPE,
		       CONVERT(nvarchar(128),CASE	WHEN (i.status & 2048) > 0 THEN ''PRIMARY KEY'' 
				WHEN (i.status & (2|4096)) > 0 THEN ''UNIQUE'' 
				ELSE '' '' END) AS UNIQUE_TYPE,
		       CONVERT(nvarchar(128),CASE	WHEN (i.status & (2048)) > 0 OR ((i.status & (4096)) > 0 ) THEN ''CONSTRAINT'' 
				ELSE ''INDEX'' END) AS INDEX_TYPE,
		       CONVERT(varchar(4000),CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 1) IS NULL THEN '''' ELSE QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 1)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 2) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 2)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 3) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 3)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 4) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 4)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 5) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 5)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 6) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 6)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 7) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 7)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 8) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 8)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 9) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 9)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 10) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 10)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 11) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 11)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 12) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 12)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 13) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 13)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 14) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 14)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 15) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 15)) END +
		       CASE	WHEN INDEX_COL(u.name+''.''+o.name, i.indid, 16) IS NULL THEN '''' ELSE '', ''+QUOTENAME(INDEX_COL(u.name+''.''+o.name, i.indid, 16)) END) AS COLUMN_NAME
	FROM dbo.sysindexes i 
		JOIN dbo.sysobjects o ON i.id = o.id
		JOIN dbo.sysusers u ON o.uid = u.uid
	WHERE i.indid <> 0
	AND o.type = ''U''
	AND i.indid < 255
	AND o.name NOT IN (''dtproperties'') AND i.name NOT LIKE ''_WA_Sys_%''')
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.DST_SCHEMATABLE') AND OBJECTPROPERTY(id, N'IsView') = 1)
	EXEC('CREATE VIEW dbo.DST_SCHEMATABLE
	AS
	SELECT CONVERT(nvarchar(128),C.TABLE_SCHEMA) AS OWNER_NAME
	,CONVERT(nvarchar(128),C.TABLE_NAME) AS TABLE_NAME
	,CONVERT(nvarchar(128),C.COLUMN_NAME) AS COLUMN_NAME
	,CONVERT(nvarchar(128),C.DATA_TYPE) AS COLUMN_DATATYPE
	,CONVERT(bit,COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA+''.''+C.TABLE_NAME), C.COLUMN_NAME ,''ISIDENTITY'')) AS ISIDENTITY
	,CONVERT(nvarchar(128),CASE WHEN COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA+''.''+C.TABLE_NAME), C.COLUMN_NAME ,''ISIDENTITY'') = 1 THEN ISNULL(CONVERT(varchar(64),IDENT_SEED(C.TABLE_SCHEMA+''.''+C.TABLE_NAME))+'',''+CONVERT(varchar(64),IDENT_INCR(C.TABLE_SCHEMA+''.''+C.TABLE_NAME)),'''') ELSE '''' END) AS IDENTSEEDINCR
	,CONVERT(bit,COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA+''.''+C.TABLE_NAME), C.COLUMN_NAME , ''ISIDNOTFORREPL'')) AS NOTFORREPLICATION
	,CONVERT(int,CASE WHEN C.CHARACTER_MAXIMUM_LENGTH IS NULL THEN CASE WHEN C.NUMERIC_PRECISION IS NULL THEN 16 ELSE C.NUMERIC_PRECISION END ELSE C.CHARACTER_MAXIMUM_LENGTH END) AS COLUMN_LENGTH
	,CONVERT(int,CASE WHEN C.NUMERIC_SCALE IS NULL THEN 0 ELSE C.NUMERIC_SCALE END) AS COLUMN_SCALE
	,CONVERT(bit,CASE WHEN C.IS_NULLABLE = ''YES'' THEN 1 ELSE 0 END) AS NULLABLE
	,CONVERT(int,C.ORDINAL_POSITION) AS ORDINAL_POSITION
	FROM INFORMATION_SCHEMA.TABLES T
		JOIN INFORMATION_SCHEMA.COLUMNS C
		ON T.TABLE_SCHEMA = C.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAME
	WHERE T.TABLE_TYPE = ''BASE TABLE'' AND T.TABLE_NAME NOT IN (''dtproperties'')
	')

GO


/*********************************************
** Creation Date: 05/29/2002
** Modif Date	: 07/14/2003
** Modif Date	: 01/20/2004
** Created By	: avigneau
** Database	: current database
** Description  : use to get physical estimate of pages usage without fragmentation.
		  
** Parameters   : none it is a select

** Compatibility: SQL Server  7.0,2000
** Remark	: 6.x has 2k page size, and I am pretty sure the optimizer would freak on the type of derived tables.
		 
** Example	: Hit the "Run" button or press F5
*********************************************/

select DT1.TABLE_NAME
--,'' as [Estimated Rows]
,RowsInTable as [Rows in Sample]
, ( 8096 ) / ((ISNULL(Fixed_Data_Size,0) + (2 + (ISNULL(Num_Variable_Cols,0) * 2) + ISNULL(Max_Var_Size,0)) + (2 + (( ISNULL(Num_Cols + 7,0)) / 8 )) +4) + 2) 
AS [Estimated Row per Page]
--,'' as [Estimated Needed Pages] 
,'8192' as [Size per Page(Bytes)]
--,'' as [Estimated Data Size(Mb)]
,ISNULL(Num_txt_Cols,0) as [Number of TXT Col] 
--,'' as [Avg size per Txt Col]
--,'' as [Estimated Txt Size(Mb)]
, ISNULL([Estimated Index Row per Page],0) AS [Estimated Index Row per Page]
--,'' as [Estimated Needed Index Pages] 
--,'' as [Estimated Index Size(Mb)]
--,'' as [Estimated Total Size(Mb)]
FROM
(
-- to get the fixed size columns length per table 
select TABLE_NAME,sum(CASE WHEN COLUMN_DATATYPE = 'int' then 4 
WHEN COLUMN_DATATYPE = 'bigint' then 8
WHEN COLUMN_DATATYPE = 'smallint' then 2
WHEN COLUMN_DATATYPE = 'tinyint' then 1   
WHEN COLUMN_DATATYPE = 'datetime' then 8
WHEN COLUMN_DATATYPE in ('ntext','text') then 16
WHEN COLUMN_DATATYPE like 'var%' then 0
else column_length end) AS Fixed_Data_Size
from dst_schematable 
where table_name not like 'dst_%'
AND COLUMN_DATATYPE not LIKE 'n[c-b-v]%'
GROUP BY TABLE_NAME
) AS DT1
INNER JOIN
(
-- to get the number of variable columns per table
select TABLE_NAME,count(*) AS Num_Variable_Cols
from dst_schematable 
where column_datatype like '%var%'
GROUP BY TABLE_NAME
) AS DT2 ON DT1.TABLE_NAME = DT2.TABLE_NAME
INNER JOIN
(
-- to get the max size of variable columns per table
select TABLE_NAME,max(CASE WHEN COLUMN_DATATYPE LIKE 'n[c-b-v]%' THEN column_length*2 ELSE column_length END ) AS Max_Var_Size
from dst_schematable 
where column_datatype like '%var%'
GROUP BY TABLE_NAME
) AS DT3 ON DT1.TABLE_NAME = DT3.TABLE_NAME
INNER JOIN
(
-- to get the number of columns per table
select TABLE_NAME,count(*) AS Num_Cols
from dst_schematable 
where column_datatype not in ('ntext','text')
GROUP BY TABLE_NAME
) AS DT4 ON DT1.TABLE_NAME = DT4.TABLE_NAME
LEFT OUTER JOIN
(
-- to get the number of blobs per table
select TABLE_NAME,count(*) AS Num_txt_Cols
from dst_schematable 
where column_datatype in ('ntext','text','image')
GROUP BY TABLE_NAME) AS DT5
ON DT1.TABLE_NAME = DT5.TABLE_NAME
LEFT OUTER JOIN
(
-- to get the estimated actual number of rows per tables in sample DB
select o.name as TABLE_NAME,MAX(i.rowcnt ) AS RowsInTable
from sysobjects o inner join sysindexes i
on o.id = i.id
group by o.name) AS DT6
ON DT1.TABLE_NAME = DT6.TABLE_NAME

LEFT OUTER JOIN(
--------------------------
SELECT DT10.TABLE_NAME
,( 8096 ) / ((ISNULL(Fixed_Key_Size,0) + (2 + (ISNULL(Num_Variable_Key_Cols,0) * 2) + ISNULL(Max_Var_Key_Size,0)) + (2 + (( ISNULL(Num_Key_Cols,0) + 7) / 8 )) + 1 + 8) + 2)
AS [Estimated Index Row per Page]
FROM(
-- to get the number of columns per index
select TABLE_NAME,count(*) AS Num_Key_Cols
FROM(
SELECT constraint_name,table_name,column_name, column_datatype,column_length
FROM(
select  i.constraint_name,t.table_name,'%'+t.column_name+'%' as tcolumn_name
, t.column_name, t.column_datatype,t.column_length
, i.table_name as itable_name, i.column_name as icolumn_name
FROM
dst_schemaindex i
INNER JOIN dst_schematable t
ON i.table_name = t.table_name 
where i.clustered_type = 'NONCLUSTERED'
) as DT1
WHERE PATINDEX(tcolumn_name,icolumn_name) > 0
) AS DT5
GROUP BY TABLE_NAME
) AS DT10
LEFT OUTER JOIN
(
-- to get the number of variable columns per index
select TABLE_NAME,count(*) AS Num_Variable_Key_Cols
FROM(
SELECT constraint_name,table_name,column_name, column_datatype,column_length
FROM(
select  i.constraint_name,t.table_name,'%'+t.column_name+'%' as tcolumn_name
, t.column_name, t.column_datatype,t.column_length
, i.table_name as itable_name, i.column_name as icolumn_name
FROM
dst_schemaindex i
INNER JOIN dst_schematable t
ON i.table_name = t.table_name 
where i.clustered_type = 'NONCLUSTERED'
) as DT1
WHERE PATINDEX(tcolumn_name,icolumn_name) > 0
) AS DT3
where column_datatype like '%var%'
GROUP BY TABLE_NAME
) AS DT11 ON DT10.TABLE_NAME = DT11.TABLE_NAME
LEFT OUTER JOIN(
-- to get the max size of variable columns per index
select TABLE_NAME,max(CASE WHEN COLUMN_DATATYPE LIKE 'n[c-b-v]%' THEN column_length*2 ELSE column_length END ) AS Max_Var_Key_Size
FROM(
SELECT constraint_name,table_name,column_name, column_datatype,column_length
FROM(
select  i.constraint_name,t.table_name,'%'+t.column_name+'%' as tcolumn_name
, t.column_name, t.column_datatype,t.column_length
, i.table_name as itable_name, i.column_name as icolumn_name
FROM
dst_schemaindex i
INNER JOIN dst_schematable t
ON i.table_name = t.table_name 
where i.clustered_type = 'NONCLUSTERED'
) as DT1
WHERE PATINDEX(tcolumn_name,icolumn_name) > 0
) AS DT4
WHERE column_datatype like '%var%'
GROUP BY TABLE_NAME
) AS DT12 ON DT10.TABLE_NAME = DT12.TABLE_NAME
LEFT OUTER JOIN(
-- to get the fixed size columns length per index 
select TABLE_NAME,sum(CASE WHEN COLUMN_DATATYPE = 'int' then 4 
WHEN COLUMN_DATATYPE = 'bigint' then 8
WHEN COLUMN_DATATYPE = 'smallint' then 2
WHEN COLUMN_DATATYPE = 'tinyint' then 1   
WHEN COLUMN_DATATYPE = 'datetime' then 8
WHEN COLUMN_DATATYPE in ('ntext','text') then 16
else column_length end) AS Fixed_Key_Size
FROM(
SELECT constraint_name,table_name,column_name, column_datatype,column_length
FROM(
select  i.constraint_name,t.table_name,'%'+t.column_name+'%' as tcolumn_name
, t.column_name, t.column_datatype,t.column_length
, i.table_name as itable_name, i.column_name as icolumn_name
FROM
dst_schemaindex i
INNER JOIN dst_schematable t
ON i.table_name = t.table_name 
where i.clustered_type = 'NONCLUSTERED'
) as DT1
WHERE PATINDEX(tcolumn_name,icolumn_name) > 0
) AS DT2
WHERE COLUMN_DATATYPE not LIKE 'n[c-b-v]%'
GROUP BY TABLE_NAME
) AS DT13 ON DT10.TABLE_NAME = DT13.TABLE_NAME
) AS DT7  ON DT1.TABLE_NAME = DT7.TABLE_NAME