1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
|
DECLARE @LaBase AS VARCHAR(MAX)
declare @NomNase as nvarchar(1000)
declare @bid as VARCHAR(MAX)
DECLARE CURSORLISTCHEMA CURSOR FOR SELECT name AS DBName FROM master.dbo.sysdatabases where dbid >5
OPEN CURSORLISTCHEMA
FETCH NEXT FROM CURSORLISTCHEMA INTO @LaBase
WHILE (@@fetch_status >= 0)
BEGIN
set @bid='use '+ @LaBase + ';'
set @bid= @bid + 'SELECT ''' + @LaBase + ''' as NomBase,OBJ.name AS OBJECT_NAME, IDX.name AS INDEX_NAME
FROM sys.index_columns AS IDXC INNER JOIN
sys.objects AS OBJ ON IDXC.object_id = OBJ.object_id INNER JOIN
sys.schemas AS SCH ON SCH.schema_id = OBJ.schema_id INNER JOIN
sys.indexes AS IDX ON IDXC.object_id = IDX.object_id AND IDXC.index_id = IDX.index_id INNER JOIN
sys.columns AS COL ON IDXC.column_id = COL.column_id AND OBJ.object_id = COL.object_id
WHERE ((OBJ.type = ''U'') OR (OBJ.type = ''V'')) and IDX.allow_page_locks=0 ;'
exec (@bid)
FETCH NEXT FROM CURSORLISTCHEMA INTO @LaBase
END
CLOSE CURSORLISTCHEMA
DEALLOCATE CURSORLISTCHEMA |
Partager