Get a list of all the Tables in a Database

Script follows, with little to no fanfare!


SELECT
        schemas.name + '.' + tables.name SchemaTableName
FROM
        sys.tables
  JOIN
        sys.schemas
     ON tables.schema_id = schemas.schema_id


Report Permissions

A reader of this blog asked me this morning: "If you wanted to design a query to make a report for management that would show which AD Groups or users have access to which reports, how would you go about it."

This is what I quickly whipped up:

SELECT
         Catalog.Name ReportName
         ,Users.UserName
         ,Roles.RoleName
   FROM [dbo].[Catalog]
         JOIN
         dbo.PolicyUserRole
         ON   [Catalog].PolicyID = PolicyUserRole.PolicyID
         JOIN
         dbo.Users
         ON   PolicyUserRole.UserID = Users.UserID
         JOIN
         dbo.Roles
         ON   PolicyUserRole.RoleID = Roles.RoleID

Can anyone see any problems with that?

Microsoft Certified Master - Database Structures

The MCM program is aimed at the uber-uber-uber SQL gurus, and from what I'm reading on various blogs it's no walk in the park for them.

The awesome thing that's popped up out of the newly revised program is that there are a bunch of free training material videos available.

While I'm not personally anywhere near thinking about attempting the MCM certification, I'm interested in hearing about what lies under the covers of SQL server from people who know it inside out. The absolute nitty gritty: like learning about DNA and mitochondria as opposed to the more systemic/anatomical view that the MCITP type courses cover.

The first video in the series covers database structures: the ways that data is stored and managed on the disk. It's presented by Paul Randal, who spent 9 years working on the storage engine for SQL server, so the info is really from the horse's mouth.

To paraphrase the summary of the video:

Records - the rows, or "slots" which make up our tables,
Pages - the 8kB chunks where the records live,
Extents - collections of 8 contiguous pages,
Allocation bitmaps - keep an eye on the extents, and;
IAM chains and allocation units - keep track of what's living where.

If you've a spare 42 minutes, check it out here.

Formatting SQL Code for Blogs

Up until recently, all the SQL code on this blog looked awful: completely lacking in formatting and unreadable (though still useful!). It looked like this:

SELECT
*
FROM
Ow.My.Eyes.Hurt

Then I discovered The Simple-Talk Code Prettifier.

You pop your formatted SQL code in (copied out of SSMS or Visual Studio), choose the style of HTML (forums in my case), whether to correct indenting and lenth of tabs etc., and voila: html code is produced which results in something real pretty like the below:

SELECT
         *
   FROM
         Oh.That.Be.Nice
         

Thanks to the HOBT (Aaron Alton) for blogging about this tool better and sooner than I.

Importing an Excel Sheet to a Database Table.

I've always found the SQL Server Import and Export Wizard a great way to pull data out of Excel into SQL Server. Except for when it doesn't work. It can be a frustrating beast, with all kinds of little idiosyncratic things to consider.

However, it's easily avoided for basic imports from Excel. If you just want a table in a SQL database which looks and feels exactly like the source data, this command is your new friend:

SELECT
         *
         INTO
         dbName.schemaName.tableName
   FROM
         OPENROWSET('Microsoft.ACE.OLEDB.12.0'
            , 'Excel 12.0;Database=excelFilePathName.xlsx'
            , [worksheetName$])

Works a charm, and avoids clicking through a GUI which tends to crash when there's any slight problems.

If you have any "missing provider" problems, which may happen on new 64 bit SQL servers, download the provider from Microsoft Access Database Engine 2010 Redistributable.

Which Reports Aren't Inherting Permissions?

Managing security settings in Reporting Services through the Report Manager website is usually pretty easy. However I've found that sometimes things go astray. Today I found a few reports which weren't inheriting permissions from their parent directory. Easy enough to fix, you jump into the security settings for that report and set "Revert to Parent Security". However, I have dozens of reports in each of a dozen directories; if I suspect this problem is going on in other reports, I'm not keen on a "needle in a haystack" search.

Here's the code for identifying which reports are no longer inheriting permissions from their parent:

USE ReportServer
SELECT
         [Path],
         [Name]
   FROM
         [dbo].[Catalog]
   WHERE
         [PolicyRoot] = 1

Another Standards Discussion: Views and Business Rules

Let’s say that the business you work for has also been around for many years, and in that time loads of little rules about the data have appeared. Market A works differently to market B. This car shouldn’t be included in this list of products. When pulling together a list of shoppers, we only care about those that have credit cards.

Every time a new project comes up, a new part of a public facing website or a new module in an in-house application, new stored procs are developed. Often, these procs are written by people who weren’t around when the DB schema involved was designed, or they haven’t got all of those little rules I discussed above memorized. Who does?

This is where Views can come in handy. Instead of building each of those annoying little rules into each new Stored Procedure that’s developed, build them into Views, and point all Stored Procs towards Views instead of Tables.

And I mean ALL Stored Procs. As a standard. If you follow this standard, you can be sure that when it’s time to implement a new business rule, you can find the appropriate View, alter some code, and all the dependant Stored Procedures will now follow that new business rule.

For example, you have a table called Lotto.Entry. It records who has entered a lottery, and how old they are. Supplying the website, there are three hundred stored procedures which access data from this table. The CEO at your company decides that it’s no longer ethical to be reporting on people under 17 who have entered the lottery. If all those 300 Stored Procedures are pointing towards the table, you’re faced with moving data around, or changing all of those procs. If the procs are instead pointing towards Lotto.vEntry, that view can have a “WHERE Entry.Age > 17” clause added. Other Views should point towards this View instead of the underlying Table, so that the business rules are “inherited” across the system.

Another advantage is that the business rules are centralized. You know to look towards the basic view of a table if you want to know how its data is handled.

Pretending to delete data is another thing that can be done in views. Let’s say you want the users of the website/UI to feel like they’re deleting data, but you want an audit trail on that data, and you simply want to hide it from the users. Add an “IsDeleted” field to your table, and a “WHERE IsDeleted = 0” clause to the base view of that table. The data is then easily “undeleted” in cases of errors.

Views are a centralized, transparent and predictable way of implementing rules in which your data is accessed.