ict.ken.be

CREATE TABLE #foo(
 name varchar(128),
 rows int,
 reserved varchar(128),
 data varchar(128),
 index_size varchar(128),
 unused varchar(128)
 )

DECLARE @objectName nvarchar(128)
 DECLARE cursorTables CURSOR FOR 
  SELECT so.name
  FROM sysobjects so (nolock), sysindexes si (nolock)
  WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name) 
  GROUP BY so.name 
  ORDER BY MAX(si.rows) DESC
 OPEN cursorTables
 FETCH NEXT FROM cursorTables INTO @objectName
 WHILE @@FETCH_STATUS = 0
 BEGIN

 INSERT INTO #foo
  EXEC ('sp_spaceused ' + @objectName)

 FETCH NEXT FROM cursorTables INTO @objectName
 END 
 CLOSE cursorTables
 DEALLOCATE cursorTables

SELECT * FROM #foo

DROP TABLE #foo

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

--only since last restart sql-server
--SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*

select *
from sys.tables
where name not in
(
select o.name
FROM sys.dm_db_index_usage_stats s
inner join sys.objects o on s.object_id = o.object_id
WHERE s.database_id = DB_ID( 'DATABASE_NAME')
--ORDER by s.last_user_update desc
)
order by modify_date desc

select *
from sys.tables t
where name like 'TMP_%'
and create_date < getdate() - 3
order by create_date desc

/* 
CREATE PROCEDURE [DBA_MESSAGENT].[SP_System_Delete_TMP_Tables]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @tableName nvarchar(128)
DECLARE cursorTables CURSOR FOR
select t.name from sys.tables t where t.Name like 'TMP_%' and create_date < getdate() - 3
OPEN cursorTables
FETCH NEXT FROM cursorTables INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN

 DECLARE @sqlSelect NVARCHAR(4000)
 SET @sqlSelect = 'DROP TABLE [' + @tableName + ']'
 EXEC SP_EXECUTESQL @sqlSelect

 FETCH NEXT FROM cursorTables INTO @tableName
END
CLOSE cursorTables
DEALLOCATE cursorTables
 
END
*/

.Net 4 repair

• winver.exe
• %windir%\Microsoft.NET\Framework64\v4.0.30319\SetupCache\Client\setup.exe /repair /x86 /x64 /ia64 /parameterfolder Client /norestart
• %windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe –i

Setting up permissions and pools on iis (eg. mojoPortal)

• Create a new Web Site in IIS and name it mojoportal, leave the IP address as "All Unassigned" but add the host name "mojoportal"
• Point the web site root to the mojoportal folder and choose a .NET 4 Integrated application pool. 
• Make note of the user that is the identity on the application pool
• In Windows Explorer, right click the mojoportal folder and choose properties, on the security tab click Edit..., then click Add..., then click Advanced..., then click Find Now
• Select the user that is the identity on the application pool, then click OK, give the user read permissions here and click OK.