TSQL: shrink all databases on SQL SERVER

 

IF EXISTS(select * from sysobjects where id = object_id(‘dbo.spShrinkAllDatabasesOnServer’) and xtype = ‘P’)
DROP PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer
GO

 

CREATE PROCEDURE [dbo].[spShrinkAllDatabasesOnServer]
AS
BEGIN

 
CREATE TABLE #TempDatabasesTable

 

(
[DatabaseName] sysname not null primary key,
Mod tinyint not null default 1
)

 

INSERT INTO #TempDatabasesTable ([DatabaseName])
SELECT name FROM sys.databases WHERE database_id not in (1,2,3,4,5,6,7,8,9,94) order by database_id

 

 

 

DECLARE @DatabaseName sysname
SET @DatabaseName = ”
WHILE @DatabaseName is not null
BEGIN

 

SET @DatabaseName = NULL

 

SELECT TOP 1 @DatabaseName = [DatabaseName] from #TempDatabasesTable where Mod = 1

 

IF @DatabaseName is NULL BREAK

 
–wenn keine offenen Connections dann verkleinern
IF isnull((SELECT count(dbid) FROM sys.sysprocesses WHERE db_name(dbid)= @DatabaseName) , 0) = 0

 

BEGIN
PRINT ‘ ‘
PRINT ‘ ‘
PRINT ‘*******************************************************************’
PRINT ‘> DB: ‘ + @DatabaseName

 

DECLARE @SqlCommand nvarchar(4000)

EXEC(
‘ USE ‘ + @DatabaseName +
‘ EXEC(”
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR LOCAL FOR
SELECT table_name FROM information_schema.tables WHERE table_type = ””base table””

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Print ””Reindexing Table: ”” + @TableName
DBCC DBREINDEX(@TableName,”” ”” , 90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
”)’)

PRINT ‘> SET RECOVERY MODE SIMPLE’

SET @SqlCommand = ‘ALTER DATABASE [‘ + @DatabaseName + ‘] SET recovery simple’
EXEC sp_executesql @SqlCommand

 

PRINT ‘> Shrinking database’

 

SET @SqlCommand = ‘dbcc shrinkdatabase([‘ + @DatabaseName + ‘])’
EXEC sp_executesql @SqlCommand

 

UPDATE #TempDatabasesTable set Mod = 0 where [DatabaseName] = @DatabaseName
END

END

 

DROP TABLE #TempDatabasesTable

 

END

 

Advertisements

TSQL: how to search for certain columnames in all tables

SELECT table_name=sysobjects.name, column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype=’U’ AND syscolumns.name like ‘%columname%’
ORDER BY sysobjects.name,syscolumns.colid

TSQL: how to search content in all columns

DECLARE @SearchStr nvarchar(100)

SET @SearchStr = ‘USER_’

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ”

SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)

 

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ”

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ‘BASE TABLE’

AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)

), ‘IsMSShipped’

) = 0

)

 

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

 

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

‘SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630)

FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +

‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2

)

END

END

END

SELECT ColumnName, ColumnValue FROM #Results

TSQL: example how to find duplicate records

WITH R
AS
(SELECT ROW_NUMBER() OVER(PARTITION BY EANNummer ORDER BY V.Artikelnummer, AuspraegungID, ltrim(EANNummer)) as ‘rownumber’, V.Artikelnummer, AuspraegungID, EANNummer

FROM ArtikelVarianten V

INNER JOIN Artikel A on V.Mandant=A.Mandant AND V.Artikelnummer= A.Artikelnummer
where A.Aktiv = -1)

SELECT  * FROM R WHERE RowNumber >1 AND NOT EANNummer IS NULL