Check Database Size

use yourdb
exec sp_spaceused


 

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'

Split

CREATE FUNCTION dbo.Split

(

      @RowData nvarchar(2000),

      @SplitOn nvarchar(5)

) 

RETURNS @RtnValue table

(

      Id int identity(1,1),

      Data nvarchar(100)

)

AS 

BEGIN

      Declare @Cnt int

      Set @Cnt = 1

      While (Charindex(@SplitOn,@RowData)>0)

      Begin

            Insert Into @RtnValue (data)

            Select

                  Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

            Set @Cnt = @Cnt + 1

      End

     

      Insert Into @RtnValue (data)

      Select Data = ltrim(rtrim(@RowData))

      Return

END

SQL Server 2005: Split string XML Style

--Here's a Split function using XML datatype.

--It's preety neat and simple compared to all others that i've seen.

--Forget While Loops and recursive CTE's.

--Enter XML:

IF OBJECT_ID('dbo.Split') IS NOT NULL

    DROP FUNCTION dbo.Split

GO

CREATE FUNCTION dbo.Split(@data NVARCHAR(MAX), @delimiter NVARCHAR(5))

RETURNS @t TABLE (data NVARCHAR(max))

AS

BEGIN

   

    DECLARE @textXML XML;

    SELECT    @textXML = CAST('' + REPLACE(@data, @delimiter, '') + '' AS XML);

    INSERT INTO @t(data)

    SELECT  T.split.value('.', 'nvarchar(max)') AS data

    FROM    @textXML.nodes('/d') T(split)

   

    RETURN

END

GO

DECLARE @text NVARCHAR(max)

SELECT @text = REPLICATE('ab,', 300) + 'ab'

SELECT * FROM dbo.Split(@text, ',')

Handling special characters with FOR XML PATH('')

Suppose I want to concatenate the list of user databases on my system, in alphabetical order.

select
  stuff(
     (select ', ' + name -- Note the lack of column name
     from sys.databases
     where database_id > 4
     order by name
     for xml path('')
     )
   , 1, 2, '') as namelist;

This is easy, and it just works. The STUFF command lets me strip the leading comma and space, and my list of database names is done.

But if I wanted to list the names of database with triangular brackets around them… then that’s bit more complicated.

select
  stuff(
     (select ', <' + name + '>'
     from sys.databases
     where database_id > 4
     order by name
     for xml path('')
     )
   , 1, 2, '') as namelist;

It still runs, but I my results don’t show the triangular brackets, it shows <databasename>, <databasename2>. It’s not what I want to see. I effectively need to HTMLDecode the results – but T-SQL doesn’t have a function like that.

However, if I actually make my FOR XML call return actual well-formed XML, then I can extract the contents out, and it returns the data to me in its original (correct) form.

select
  stuff(
     (select ', <' + name + '>'
     from sys.databases
     where database_id > 4
     order by name
     for xml path(''), root('MyString'), type      ).value('/MyString[1]','varchar(max)')
   , 1, 2, '') as namelist;

To be valid XML, it needs a ROOT element. I also need to tell it to be XML, using the TYPE directive.

But this lets me hook into the value of /MyString[1], and return that as varchar(max).

And it works, my data comes back as , , etc.

I can also skip the ROOT element (but keep TYPE) and use .value('(./text())[1]','varchar(max)') - but for some reason it's always felt strange to me to be returning something which I declare to be XML (using TYPE) without it being well-formed.