SELECT 	CASE 
		WHEN GROUPING(hostname) = 1 THEN 'TOTAL'
		ELSE hostname
	END AS Host, 
	db_name(dbid), 
	COUNT(*) AS cnt
FROM master.dbo.sysprocesses
GROUP BY hostname, dbid WITH ROLLUP
HAVING GROUPING(dbid) = 0 OR GROUPING(hostname) = 1
ORDER BY CASE WHEN GROUPING(hostname) = 1 THEN 1 ELSE 0 END, hostname , dbid

DECLARE @name sysname
 
SELECT @name = ''
 
WHILE @name < (SELECT MAX(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES 
	WHERE TABLE_TYPE = 'VIEW'
	AND TABLE_NAME LIKE 'syncobj_%')
BEGIN
	SELECT @name = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES 
		WHERE TABLE_TYPE = 'VIEW'
		AND TABLE_NAME LIKE 'syncobj_%' AND TABLE_NAME > @name
 
	EXEC('drop view ' + @name)
 
END

USE dabaseName
GO
 
DECLARE cur CURSOR
FAST_FORWARD
FOR
SELECT l.name
FROM master.dbo.sysxlogins l
LEFT JOIN sysusers u ON u.sid = l.sid
WHERE
u.sid IS NULL AND
l.name IS NOT NULL AND
l.sid IS NOT NULL
ORDER BY l.name
 
DECLARE @name sysname
OPEN cur
 
FETCH NEXT FROM cur INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
                EXEC sp_grantdbaccess @name
        END
        FETCH NEXT FROM cur INTO @name
END
 
CLOSE cur
DEALLOCATE cur
GO

Crée juste les appels de sp à lancer

USE DATABASE
GO
 
DECLARE @RoleName sysname
SET @RoleName = 'role_name'
 
CREATE TABLE #temp
(role_col nvarchar(132) NOT NULL,
member nvarchar(132) NOT NULL,
id_col nvarchar(176))
 
INSERT INTO #temp (role_col, member, id_col)
exec sp_helprolemember @RoleName
 
SELECT 'exec sp_addrolemember N''' + @RoleName + ''', N''' + s.name + ''''
FROM dbo.sysusers s
LEFT JOIN #temp t ON s.name = t.member
WHERE s.STATUS = 2
AND t.member IS NULL
AND s.name NOT IN ('guest', 'dbo')
 
DROP TABLE #temp

l'exemple suivant mets toutes les bases en lecture seule.

SELECT 'ALTER DATABASE ' + name + ' SET READ_ONLY' + char(10) + 'GO' +
char(10)
FROM master.dbo.sysdatabases
WHERE dbid > 4
ORDER BY Name

Un exemple de code qui génère des instructions pour vider une base. Supprime d'abord les contraintes de DRI, truncate les tables, shrink la base

-- CLEAN AND EMPTY DATABASE
 
/*
SELECT
t.name as tbl,
c.name as fkey
FROM sysforeignkeys fk
JOIN sysobjects c ON fk.constid = c.id
JOIN sysobjects t ON fk.fkeyid = t.id
*/
 
SELECT
'ALTER TABLE ' + t.name + ' DROP CONSTRAINT ' + c.name
FROM sysforeignkeys fk
JOIN sysobjects c ON fk.constid = c.id
JOIN sysobjects t ON fk.fkeyid = t.id
 
 
SELECT 'TRUNCATE TABLE ' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
 
DBCC SHRINKDATABASE ('dbname')
DBCC SHRINKFILE (dbname_Log, 10)

Génère des instructions pour insérer, sur un autre serveur, des catégories de jobs personnalisées.

SELECT 'EXEC msdb.dbo.sp_add_category ''JOB'', ''LOCAL'', ''' + name + ''''
FROM msdb.dbo.syscategories
WHERE category_Id > 99
ORDER BY category_Id

… quand vous avez un accès sysadmin.

CREATE TABLE #tmp (Drive char(1), MBFree int)
DECLARE @Drive char(1)
DECLARE @sql varchar(100)
 
INSERT INTO #tmp
EXEC master.dbo.xp_fixeddrives
 
WHILE EXISTS (SELECT 1 FROM #tmp) BEGIN
        SELECT TOP 1 @Drive = Drive FROM #tmp
        SET @sql = @Drive + ':\'
        SELECT 'Content of drive ' + @sql
        EXEC master.dbo.xp_dirtree @sql
        DELETE FROM #tmp WHERE Drive = @Drive
END
 
DROP TABLE #tmp

liste les bases et roles dans lequel un user est présent

DECLARE @user AS sysname,
@sql AS varchar(1000)
 
CREATE TABLE ##roles (DBName sysname NULL, RoleName sysname NULL, UserName sysname NULL)
 
SET @user = '%myname%'
 
SET @sql = 'INSERT INTO ##roles SELECT ''?'' as DBName, r.name as RoleName, u.name as UserName
FROM ?.dbo.sysusers u
LEFT JOIN (?.dbo.sysmembers m
JOIN ?.dbo.sysusers r ON m.groupuid = r.uid AND r.issqlrole = 1) ON u.uid = m.memberuid
WHERE (u.name LIKE ''' + @user + ''' OR
r.name LIKE '''+ @user + ''')'
EXEC sp_MSforeachdb @sql
 
SELECT *
FROM ##roles
ORDER BY DBName, RoleName, UserName
 
DROP TABLE ##roles

Liste les privilèges accordés à un user sur tous les objets de toutes les bases

DECLARE @user AS sysname,
@sql AS varchar(1000)
 
CREATE TABLE ##roles (DBName sysname NULL, UserName sysname NULL, ObjectType char(3) NULL, ObjectNameName sysname NULL, Action varchar(30) NULL, ProtectType varchar(10) NULL)
 
SET @user = '%myname%'
 
SET @sql = '
INSERT INTO ##roles
SELECT ''?'' as DBName, u.name as UserName, o.type, o.name as ObjectName,
CASE p.action
WHEN 26 THEN ''REFERENCES''
WHEN 178 THEN ''CREATE FUNCTION''
WHEN 193 THEN ''SELECT''
WHEN 195 THEN ''INSERT''
WHEN 196 THEN ''DELETE''
WHEN 197 THEN ''UPDATE''
WHEN 198 THEN ''CREATE TABLE''
WHEN 203 THEN ''CREATE DATABASE''
WHEN 207 THEN ''CREATE VIEW''
WHEN 222 THEN ''CREATE PROCEDURE''
WHEN 224 THEN ''EXECUTE''
WHEN 228 THEN ''BACKUP DATABASE''
WHEN 233 THEN ''CREATE DEFAULT''
WHEN 235 THEN ''BACKUP LOG''
WHEN 236 THEN ''CREATE RULE''
END as Action,
CASE p.protecttype
WHEN 204 THEN ''GRANT_W_GRANT''
WHEN 205 THEN ''GRANT''
WHEN 206 THEN ''REVOKE''
END as ProtectType
FROM ?.dbo.sysusers u
JOIN ?.dbo.sysprotects p ON u.uid = p.uid
JOIN ?.dbo.sysobjects o ON p.id = o.id
WHERE u.name LIKE ''' + @user + ''''
EXEC sp_MSforeachdb @sql
 
SELECT *
FROM ##roles
ORDER BY 1, 2, 3
 
DROP TABLE ##roles

SELECT name, cast(used*8 AS varchar(15)) + ' ko' AS [space used], rowcnt AS [Rows]
FROM dbo.sysindexes
WHERE name = 'indexname' /* pour un index spécifique */
ORDER BY name /* pour tous les indexes */

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY TABLE_NAME

SELECT *
FROM sysindexes
WHERE id=object_id('view_name')

EXEC sp_MSforeachdb 'IF DATABASEPROPERTYEX(''?'', ''status'') = ''ONLINE'' AND ''?'' NOT IN(''master'', ''model'',''tempdb'',''msdb'') BACKUP DATABASE ?'

Ecrite par Dan Guzman, MVP. Trouvée sur usenet et copiée ici pour référence.

SELECT
    pk.TABLE_SCHEMA AS PK_TableSchema,
    pk.TABLE_NAME AS PK_TableName,
    pk_col.COLUMN_NAME AS PK_ColumnName,
    fk.CONSTRAINT_NAME AS FK_ConstraintName,
    fk.TABLE_SCHEMA AS FK_TableSchema,
    fk.TABLE_NAME AS FK_TableName,
    fk_col.COLUMN_NAME AS FK_ColumnName,
    pk_col.ORDINAL_POSITION AS OrdinalPosition
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk_col ON
    pk_col.CONSTRAINT_SCHEMA = pk.CONSTRAINT_SCHEMA AND
    pk_col.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON
    rc.UNIQUE_CONSTRAINT_SCHEMA = pk.CONSTRAINT_SCHEMA AND
    rc.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON
    fk.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA AND
    fk.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fk_col ON
    fk_col.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA AND
    fk_col.CONSTRAINT_NAME = rc.CONSTRAINT_NAME AND
    fk_col.ORDINAL_POSITION = pk_col.ORDINAL_POSITION
WHERE
    pk.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
    fk.CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY
    pk.TABLE_SCHEMA,
    pk.TABLE_NAME,
    fk.CONSTRAINT_NAME,
    fk.TABLE_SCHEMA,
    fk.TABLE_NAME,
    pk_col.ORDINAL_POSITION

Avec, en prime, l'indication du Identity

SELECT	c.TABLE_NAME,
	k.COLUMN_NAME,
	COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME), k.COLUMN_NAME, 'IsIdentity') AS IS_IDENTITY
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME
WHERE c.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY c.TABLE_NAME, k.COLUMN_NAME

Bases de données du serveur sur lesquelles il n'y a pas de backup

SELECT db.CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA db
LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = db.CATALOG_NAME
WHERE bs.database_name IS NULL
ORDER BY db.CATALOG_NAME