Size of all databases on one SQL Server

December 4, 2011 by Leave a Comment

Here is a script that I created to get the size of all of the databases on one SQL Server.

Generally I stay away from temp tables, especially global temp tables, but I didn’t see a good way to do this without them.

CREATE TABLE ##alldatabasesizes
  (
     dbname    VARCHAR(1024),
     type_desc VARCHAR(1024),
     name      VARCHAR(1024),
     size      INTEGER
  );
EXECUTE Sp_msforeachdb
'INSERT INTO ##AllDatabaseSizes 
SELECT db_name() as dbName, type_desc, 
name, size FROM [?].sys.database_files'
SELECT *
FROM   ##alldatabasesizes;
DROP TABLE ##alldatabasesizes

Filed under TSQL Tagged with , ,

Determining free disk space with TSQL

December 3, 2011 by Leave a Comment

Working on a new report for the SQL Server Health reports, I needed to display the amount of free disk space on a SQL Server.

EXEC MASTER..Xp_fixeddrives;

Which was useful if I just wanted to look, but I needed to use the results in a query, and I didn’t want to put the results into a temp table, so here is how I decided to do it, using table variables.

 

DECLARE @disk_space TABLE(drive CHAR(1) NOT NULL, free INTEGER NOT NULL)
INSERT INTO @disk_space
EXEC MASTER..Xp_fixeddrives;

SELECT drive,
CAST(free* 10 / 1024 AS FLOAT) / 10 AS gbfree
FROM @disk_space
ORDER BY gbfree ASC;

With this, I am now about to query the @disk_space table variable and join it with other tables in the database.

Filed under DBA, TSQL Tagged with , , , , , ,

Recursive CTE’s

November 29, 2011 by 2 Comments

As part of my planning for the SQL Saturday Presentation in Vancouver I am creating an hour long presentation on Common Table Expressions.

The easiest way to do a recursive query in SQL server is to use a recursive CTE (Common Table Expression).

What is a Common Table Expression in SQL Server?

  • Similar to the ease of a temporary table without the performance problems of a temp table.
  • Temporary Named Result Set
  • Acts like a temporary view
  • Can be use to
    • Create a recursive query
    • Simplify a query by using a result set multiple times
    • Self JOIN a subquery without running the subquery twice

Here is a transact SQL sample based on the AdventureWorksLt database demonstrating a CTE for a recursive query.

WITH CategoryCTE(CategoryId, Name, Level) AS (
SELECT ProductCategoryId AS CatId,
Name, 0 as Level
FROM saleslt.ProductCategory
WHERE ParentProductCategoryId is NULL
UNION ALL -- and now for the recursive part 
SELECT c.ProductCategoryId AS CatId,
c.Name, ccte.CategoryId + 1 as Level
FROM saleslt.ProductCategory c
INNER JOIN CategoryCTE as ccte
ON ccte.CategoryId = c.ParentProductCategoryID
)
SELECT *
FROM CategoryCTE
ORDER BY Level, Name;

 

CTE’s and Recursive CTE’s were first introduced in SQL Server 2005, and are available in 2008 and 2008R2 with the same syntax.

 

 

For another example of CTE’s (non-recursive) take a look at the query behind the Backup Set SQL Server Health Report.

Filed under CTE, SQL Saturday, TSQL Tagged with , , , , ,

A Fun Thanksgiving Day Query.

November 24, 2011 by Leave a Comment

Download the Thanksgiving query sample code here.

I thought it would be fun to put together a query to get us in the spirit of Thanksgiving dinner tonight.

This query is using techniques from another recent posting on CSV formatting query output.

 

Here is my Thanksgiving gift to you, the Thanksgiving day query.

 

Don’t copy and paste, your browser may mess up the single quotes… Instead download the Thanksgiving query sample code here.

-- a fun Thanksgiving day sql query
SELECT 'turkey'
UNION
SELECT 'stuffing'
UNION
SELECT 'cranberry sauce'
UNION
SELECT 'yams'
UNION
SELECT 'sweet potato pie'
UNION
SELECT 'gravy'
UNION
SELECT 'beans'
UNION
SELECT 'green bean casserole'
UNION
SELECT 'mashed potatos'
UNION
SELECT 'collard greens'
UNION
SELECT 'beer'
UNION
SELECT 'cider'
UNION
SELECT 'pumpkin pie';

GO

-- but thats too much food, 
--so lets keep on the diet track with only 5 items for turkey day dinner

SELECT TOP 5 fooditem FROM (
SELECT 'turkey' AS fooditem
UNION
SELECT 'stuffing'
UNION
SELECT 'cranberry sauce'
UNION
SELECT 'yams'
UNION
SELECT 'sweet potato pie'
UNION
SELECT 'gravy'
UNION
SELECT 'beans'
UNION
SELECT 'green bean casserole'
UNION
SELECT 'mashed potatos'
UNION
SELECT 'collard greens'
UNION
SELECT 'beer'
UNION
SELECT 'cider'
UNION
SELECT 'pumpkin pie' ) AS t1
ORDER BY Newid(); -- throw in some random ordering

GO
-- ok, but lets throw it into a comma delimited list rather than a table

SELECT Substring((SELECT ', ' + fooditem
FROM (
SELECT TOP 5 fooditem FROM (
SELECT 'turkey' AS fooditem
UNION
SELECT 'stuffing'
UNION
SELECT 'cranberry sauce'
UNION
SELECT 'yams'
UNION
SELECT 'sweet potato pie'
UNION
SELECT 'gravy'
UNION
SELECT 'beans'
UNION
SELECT 'green bean casserole'
UNION
SELECT 'mashed potatos'
UNION
SELECT 'collard greens'
UNION
SELECT 'beer'
UNION
SELECT 'cider'
UNION
SELECT 'pumpkin pie' ) AS t1 ORDER BY Newid()) AS t2
FOR XML PATH('')),3,10000000) AS list

 

Here is the output…

Happy Thanksgiving Everyone!

Filed under TSQL Tagged with , , ,

Converting part of a result set to a comma separated list

October 22, 2011 by 2 Comments

One of the common task that I have come across is the need to convert a list of results into a comma separated list.  There are many ways to use this, one if which is in reports.

Download the AsCSV.sql sample here.

Often times I have told developers, here is how you do it, and if you Google on it you will find some great samples.  Now it is time that I provide my own sample. I call this the FOR XML hack since it used the FOR XML command, without actually involving any XML at all.

-- first query showing columns 
SELECT c.firstname + ' ' + c.lastname
  FROM  [AdventureWorks].[HumanResources].[Employee] e
  INNER JOIN [AdventureWorks].[Person].[Contact] c
          ON c.contactid = e.contactid
  ORDER BY c.lastname;
 GO

-- second query as csv 
SELECT Substring((SELECT ', ' + c.firstname + ' ' + c.lastname
  FROM  [AdventureWorks].[HumanResources].[Employee] e
  INNER JOIN [AdventureWorks].[Person].[Contact] c
          ON c.contactid = e.contactid
  ORDER BY c.lastname
FOR XML PATH('')),3,10000000) AS list;

The output from the above queries will look like this.

 

Although that is useful, and I have on many occasions used a very similar query, where its gets interesting is to create a comma seperated list as part of a result set, not as the whole result set.

 

For example if you have the following list, it would be interesting to show a csv list of employees that have each title.

SELECT e.title, c.firstname + ' ' + c.lastname
  FROM  [AdventureWorks].[HumanResources].[Employee] e
  INNER JOIN [AdventureWorks].[Person].[Contact] c
          ON c.contactid = e.contactid
  ORDER BY e.title;


But I want it to look like this…

 

You can do it with a sub query in the list of columns.

 

SELECT e.title,

( SELECT Substring((SELECT ', ' + c1.firstname + ' ' + c1.lastname
  FROM  [AdventureWorks].[HumanResources].[Employee] e1
  INNER JOIN [AdventureWorks].[Person].[Contact] c1
          ON c1.contactid = e1.contactid
  WHERE e1.title = e.title
  ORDER BY c1.lastname
FOR XML PATH('')),3,10000000) AS list ) AS employeelist

  FROM  [AdventureWorks].[HumanResources].[Employee] e
  GROUP BY e.title;

So even if you can do the CSV formatting in a web page before it is displayed to the user, it may be easier to do it in the database like this.

Filed under TSQL Tagged with , , ,

Finding the names of all indexes on a SQL Server

June 11, 2011 by Leave a Comment

I needed a way to list of of the indexes for an entire database, but I was running into a problem using sys.indexes that I could only see the indexes for the current database. Below is the solution that I put together to list off of the indexes in the entire database.


CREATE TABLE ##allindexes
(
databse_id INTEGER,
table_id INTEGER,
index_id INTEGER,
table_name      VARCHAR(1024)
);
EXECUTE Sp_msforeachdb
'use [?];
INSERT INTO ##AllIndexes
select DB_ID() as database_id, object_id as table_id, index_id, name
from sys.indexes
where name is not null'

SELECT *
FROM   ##allindexes;
DROP TABLE ##allindexes;

 

Filed under TSQL Tagged with ,

← Older posts