Help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with query

Author

Topic

martinborjesson
Starting Member

3 Posts

Posted - 2010-10-13 : 08:34:11

Hello,
I need to document every sql server for our company. its probably about 100 servers.

im new to t-sql and need som help. im looking for a query that can give me this data presented in a way so i just can copy & paste it to word.

DB Name, Size, Data file location, Log file location, Recovery model
(for all databases on server)

please help

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-13 : 09:05:29

Some of the information you can get from sys.databases.

Select * from master.sys.databases

For some properties like Recovery Model you can use function : DATABASEPROPERTYEX

I don't have any sql instance currently to frame the query.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-13 : 09:24:26

Here's something to get you started


DECLARE @sql NVARCHAR(MAX)
DECLARE @dbName SYSNAME

IF OBJECT_ID('tempDb..#dbDetails') IS NOT NULL DROP TABLE #dbDetails
CREATE TABLE #dbDetails (
	[server] SYSNAME
	, [dbName] SYSNAME
	, [fileType] VARCHAR(20)
	, [filename] VARCHAR(MAX)
	, [size] BIGINT
	)

DECLARE dbCursor CURSOR LOCAL READ_ONLY FOR
SELECT [name] FROM sys.databases

OPEN dbCursor

	FETCH NEXT FROM dbCursor INTO @dbName

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		SET @sql = N'
		USE ' + QUOTENAME(@dbName) + '
		
		INSERT INTO #dbDetails (
			[server]
			, [dbName]
			, [fileType]
			, [filename]
			, [size]
			)
		SELECT
			@@SERVERNAME
			, DB_NAME()
			, type_desc
			, physical_name
			, size
		FROM
			sys.database_files'

		EXEC (@sql)

		FETCH NEXT FROM dbCursor INTO @dbName
	END

CLOSE dbCursor
DEALLOCATE dbCursor

SELECT * FROM #dbDetails

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

martinborjesson
Starting Member

3 Posts

Posted - 2010-10-13 : 10:00:48

Thanks! I'll try the DATABASEPROPERTYEX.

martinborjesson
Starting Member

3 Posts

Posted - 2010-10-13 : 10:02:44

Thank you! really appreciate it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources