SQL Server database, table memory, instance name parsing

--Database memory analysis use Mastergodeclare @insSize TABLE (dbName sysname,checktime varchar, dbsize varchar (), logsize varchar ( INSERT into @insSize (DbName, Checktime, Dbsize, logsize) EXEC sp_msforeachdb ' select '? ' Dbname,convert (VARCHAR (19 ), GETDATE (), Checktime,ltrim (STR (filename,3) <> "LDF" then convert (Dec (15,2), size) * 8/ (15,2) (+ ELSE 0 END), + "MB") Dbsize, LTRIM (STR (Case-Right (filename,3) = "LDF" then convert (Dec (15,2), size) * 8/1024 ELSE 0 END), 15,2) + ' MB ') logsize from?. Dbo.sysfiles '--select * from @insSize ORDER by CONVERT (Decimal,ltrim (RTRIM (SUBSTRING (Dbsize,1,len)-2))) Descselect checktime as ' check Date ', dbName as ' database ', dbsize as ' data capacity ', logsize as ' log capacity ', cast (cast (left (Dbsize,len (dbsize)-2) & Nbsp;as decimal (10,2)) +cast (left (Logsize,len (logsize)-2)  as decimal (10,2)) as VARCHAR (+) + ' MB ' as ' Occupy space ' from the @insSize WHERE dbName not in (' Master ', ' model ', ' tempdb ', ' msdb ', ' distribution ') ORDER by CONVERT (Decimal,ltrim (RTRIM (SUBSTRING (Dbsize,1,len (dbsize)-2)) desc--table Space Analysis Beginuse[empi]--select count (1) from  Sysobjects the number of tables in the where xtype= ' U ' database create table #t (name varchar (255), rows bigint, reserved varchar (), data varchar (20), Index_size varchar (unused varchar) EXEC sp_msforeachtable "insert into #t exec sp_spaceused '? '" SELECT Name as ' Table name ', rows as ' data rows ', cast (cast (CAST (Data,len (data)-2 as FLOAT)/1024 as DECIMAL (10,3)) as VARCHAR () + ' MB ' as ' Occupy space ' , Cast (CAST (CAST (Reserved,len (reserved)-2 as FLOAT)/1024 as DECIMAL (10,3) as VARCHAR) + ' MB ' as ' total space reserved ', index _size as ' indexed space ', unused as ' unused space ' from the #t ORDER by CONVERT (Decimal,ltrim (RTRIM (SUBSTRING (data)-2))) Data,1,len Table #tend-User name and permissions (not all) use Mastergoselect u.name as ' username ', g.name as ' permissions ', u.default_database_name as ' default database ' from SYS.S Erver_principals u, sys.server_principals G, sys.server_role_members m where g.principal_id = m.role_principal_id and U. principal_id = m.member_principal_id Order bY 1, 2go--DB instance name SELECT @ @SERVICENAME 

　　

SQL Server database, table memory, instance name parsing