Exploring your database schema with SQL

–find the actual code for a particular stored procedure, view, function etc.

Select object_Name(object_ID),definition from sys.SQL_Modules

where object_Name(object_ID)=’MyObjectName’

Finding Tables with no Primary Keys

You’ll want to know if there tables without primary keys and why. Here is a way of getting that information from theINFORMATION_SCHEMA.tables view.

–Which of my tables don’t have primary keys?

SELECT –we’ll do it via information_Schema

TheTables.Table_Catalog+’.’+TheTables.Table_Schema+’.’

+TheTables.Table_Name AS [tables without primary keys]

FROM

information_Schema.tables TheTables

LEFT OUTER JOIN information_Schema.table_constraints TheConstraints

ON TheTables.table_Schema=TheConstraints.table_schema

AND TheTables.table_name=TheConstraints.table_name

AND constraint_type=’PRIMARY KEY’

WHERE table_Type=’BASE TABLE’

AND constraint_name IS NULL

ORDER BY [tables without primary keys]

The following code, using a Catalog view, should give the same result as the previous code, but much more easily. The TableHasPrimaryKey property of the OBJECTPROPERTY function simply returns 1 if a primary key exists, or 0 if not.

— you can save a lot of code by using the catalog views

— along with the OBJECTPROPERTY() function

Select

DB_NAME()+’.’+Object_Schema_name(t.object_ID)+’.’

+t.name  as [tables without primary keys]

FROM sys.tables t

WHERE OBJECTPROPERTY(object_id,’TableHasPrimaryKey’) = 0

ORDER BY [tables without primary keys]

Finding Tables with no Referential Constraints

You can, of course use almost the same query to explore many other characteristics of the tables. You’d certainly want to investigate any tables that seem to have no referential constraints, either as a key or a foreign reference.

–Which of my table are waifs (No Referential constraints)

SELECT

DB_NAME()+’.’+Object_Schema_name(t.object_ID)+’.’+t.name AS [Waif Tables]

FROM

sys.tables t

WHERE

OBJECTPROPERTY(object_id, ‘TableHasForeignKey’)=0

AND OBJECTPROPERTY(object_id, ‘TableHasForeignRef’)=0

AND OBJECTPROPERTY(object_id, ‘IsUserTable’)=1

ORDER BY

[Waif tables]

Finding Tables with no Indexes

You’d also be interested in those tables without clustered indexes and want to find out the reason why.

SELECT

DB_NAME()+’.’+Object_Schema_name(t.object_ID)+’.’+t.name AS [Tables without Clustered index]

FROM

sys.tables t

WHERE

OBJECTPROPERTY(object_id, ‘TableHasClustIndex’)=0

order by [Tables without Clustered index]

And you’d scratch your head a bit if there were tables of any great size without any index at all.

SELECT

DB_NAME()+’.’+Object_Schema_name(t.object_ID)+’.’+t.name AS [Tables without any index]

FROM

sys.tables t

WHERE

OBJECTPROPERTY(object_id, ‘TableHasIndex’)=0

order by [Tables without any index]

A one-stop View of your Table Structures

We can pull of this together in a single query against the sys.tables Catalog view to find out which objects (indexes, constraints and so on) do and don’t exist on a given database. This is a handy query to get a summary of the characteristics of your tables’ structure at a quick glance.

SELECT

DB_NAME()+’.’+Object_Schema_name(t.object_ID)+’.’+t.name  AS [Qualified Name],

CASE WHEN OBJECTPROPERTY(object_id,’TableHasActiveFulltextIndex’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [FT index],–Table has an active full-text index.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasCheckCnst’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Check Cnt],–Table has a CHECK constraint.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasClustIndex’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Clustered ix],–Table has a clustered index.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasDefaultCnst’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Default Cnt],–Table has a DEFAULT constraint.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasDeleteTrigger’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Delete Tgr],–Table has a DELETE trigger.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasForeignKey’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [FK Cnt],–Table has a FOREIGN KEY constraint.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasForeignRef’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [FK Ref],–referenced by a FOREIGN KEY constraint.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasIdentity’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Identity Col],–Table has an identity column.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasIndex’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Any index],–Table has an index of any type.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasInsertTrigger’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Insert Tgr],–Object has an INSERT trigger.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasNonclustIndex’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [nonCl Index],–Table has a nonclustered index.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasPrimaryKey’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Primary Key],–Table has a primary key

CASE WHEN OBJECTPROPERTY(object_id,’TableHasRowGuidCol’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [ROWGUIDCOL],–ROWGUIDCOL for uniqueidentifier col

CASE WHEN OBJECTPROPERTY(object_id,’TableHasTextImage’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Has Blob],–Table has text, ntext, or image column

CASE WHEN OBJECTPROPERTY(object_id,’TableHasTimestamp’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Timestamp],–Table has a timestamp column.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasUniqueCnst’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Unique Cnt],–Table has a UNIQUE constraint.

CASE WHEN OBJECTPROPERTY(object_id,’TableHasUpdateTrigger’) = 0

THEN ‘no’ ELSE ‘yes’ END AS  [Update Tgr]–Table has an Update trigger.

FROM sys.tables t

ORDER BY [Qualified Name]

How many of each Object…

Since the OBJECTPROPERTY function generally returns either a 1 or a 0, it can be used pretty simply in order to find out not just whether there are constraints, defaults, rules or triggers on individual tables, but also how many of them there are.

–Which of my tables have constraints, defaults, rules or triggers on them? If so, then how many?

SELECT

DB_NAME()+’.’+Object_Schema_name(s.[object_ID])+’.’+p.name AS [Qualified_Name],

Count(*),

sum(OBJECTPROPERTY ( s.object_ID , ‘IsPrimaryKey’)) as [Pk],

sum(OBJECTPROPERTY ( s.object_ID , ‘IsCheckCnst’)) as [ChkCns],

sum(OBJECTPROPERTY ( s.object_ID , ‘IsDefaultCnst’)) as [DefCns],

sum(OBJECTPROPERTY ( s.object_ID , ‘IsForeignKey’)) as [Fk],

sum(OBJECTPROPERTY ( s.object_ID , ‘IsConstraint’)) as [Cnstrnt],

sum(OBJECTPROPERTY ( s.object_ID , ‘IsDefault’)) as [Default],

sum(OBJECTPROPERTY ( s.object_ID , ‘IsTrigger’)) as [Trigger]

FROM

sys.objects S –to get the objects

inner JOIN sys.objects p

–to get the parent object so as to get the name of the table

ON s.parent_Object_ID=p.[object_ID]

WHERE

OBJECTPROPERTY ( p.object_ID , ‘IsTable’)<>0

GROUP BY

DB_NAME()+’.’+Object_Schema_name(s.[object_ID])+’.’+p.name

Too many Indexes…

By a slightly different route, we can also find out which of our tables have the most indexes on them. Are any of them duplications? Here is a query you might use to see where the indexes might have gathered in undue numbers.

–Which of my tables have the most indexes?

SELECT TOP 10

COUNT(*) AS [Indexes],

DB_NAME()+’.’+Object_Schema_name(t.object_ID)+’.’+t.name AS [table]

FROM

sys.indexes i

INNER JOIN sys.objects t

ON i.object_ID=t.object_ID

WHERE

USER_NAME(OBJECTPROPERTY(i.object_id, ‘OwnerId’)) NOT LIKE ‘sys%’

GROUP BY

DB_NAME()+’.’+Object_Schema_name(t.object_ID)+’.’+t.name

ORDER BY

COUNT(*) DESC

Seeking out Troublesome Triggers

I find triggers particularly troublesome as it is not always obvious that they are there. I’m not the only developer who has spent an hour trying to work out why the result of an update is nothing like what one was expecting, only to be struck by the thought that some crazed code-jockey has inexplicably placed an update trigger on one of your tables. Yes, there it is. The following code should winkle out these lurking problems, and much more besides.

–Which of my tables have triggers on them, and how many?

SELECT –firstly, we’ll search the names of the basic objects

DB_NAME()+’.’+Object_Schema_name(s.[object_ID])+p.name AS [Qualified_Name],

COUNT(*) AS [how many]

FROM

sys.objects S –to get the objects

INNER JOIN sys.objects p

–to get the parent object so as to get the name of the table

ON s.parent_Object_ID=p.[object_ID]

WHERE

OBJECTPROPERTY ( s.object_ID , ‘IsTrigger’)<>0

and OBJECTPROPERTY ( p.object_ID , ‘IsTable’)<>0

GROUP BY

DB_NAME()+’.’+Object_Schema_name(s.[object_ID])+p.name

.. and from this, you can drill down to  see the sort of triggers your tables have:

SELECT

DB_NAME()+’.’+Object_Schema_name(t.[object_ID])+’.’+t.name AS [Qualified_Name],

case when OBJECTPROPERTY ( t.object_ID , ‘HasAfterTrigger’)<>0

then ‘yes’ else ‘no’ end as [After],

case when OBJECTPROPERTY ( t.object_ID , ‘HasDeleteTrigger’) <>0

then ‘yes’ else ‘no’ end as  [Delete],

case when OBJECTPROPERTY ( t.object_ID , ‘HasInsertTrigger’) <>0

then ‘yes’ else ‘no’ end as  [Insert],

case when OBJECTPROPERTY ( t.object_ID , ‘HasInsteadOfTrigger’) <>0

then ‘yes’ else ‘no’ end as [Instead Of],

case when OBJECTPROPERTY ( t.object_ID , ‘HasUpdateTrigger ‘)<>0

then ‘yes’ else ‘no’ end as [Update]

FROM

sys.tables t

Querying the Documentation in Extended Properties

Catalog queries are a powerful way of querying the documentation in order to find out more about the business rules governing the database structure. There are several useful queries that you can use if you have been sensible enough to structure your documentation, such as listing out your procedures and functions, along with a brief synopsis of how they are used and why. Here, we’ll just restrict ourselves to a useful list of all the tables that have no documentation in the extended properties. There really aren’t any other places to put your table documentation so you can be fairly sure that these tables have no documentation.

–Which tables do not have any documentation in extended properties

SELECT

DB_NAME()+’.’+Object_Schema_name(s.[object_ID])+’.’+s.name AS [Undocumented Table]

FROM

sys.objects s

LEFT OUTER JOIN sys.extended_properties ep

ON s.object_ID=ep.major_ID

AND minor_ID=0

WHERE

type_desc=’USER_TABLE’

AND ep.value IS NULL

Object Permissions and Owners

There are a whole variety of things you will need information about as well as the details of the database objects; lists of permissions on each object and the type of permissions they represent, for example. Here is a query that lists the database-level permissions for the users (or particular user, if the final condition that is currently commented out is used.)

SELECT

CASE WHEN class_desc=’DATABASE’ THEN DB_NAME()

WHEN class_desc=’SCHEMA’ THEN SCHEMA_NAME(major_id)

WHEN class_desc=’OBJECT_OR_COLUMN’ THEN OBJECT_NAME(major_id)

WHEN class_desc=’DATABASE_PRINCIPAL’ THEN USER_NAME(major_id)

WHEN class_desc=’TYPE’ THEN TYPE_NAME(major_id)

ELSE ‘Huh??’

END, USER_NAME(grantee_principal_id) AS grantee,

USER_NAME(grantor_principal_id) AS grantor, type, Permission_Name,

State_Desc

FROM

sys.database_permissions

WHERE

Class_Desc IN (‘DATABASE’, ‘SCHEMA’, ‘OBJECT_OR_COLUMN’,

‘DATABASE_PRINCIPAL’, ‘TYPE’)

— and grantee_principal_id = DATABASE_PRINCIPAL_ID(‘public’);

A different task is to explore the ownership of the various objects in your database. The following code will make this task a lot simpler.

–find the user names of all the objects

Select [Entity Type], [Owner name], [Object Name]

from

(

SELECT replace(SUBSTRING(v.name, 5, 31),’cns’,’constraint’)  AS [entity type]

,USER_NAME(OBJECTPROPERTY(object_id, ‘OwnerId’)) AS [owner name]

,DB_NAME()+’.’+Object_Schema_name(o.object_ID)+’.’+o.name as [Object Name]

FROM sys.objects o

LEFT OUTER JOIN master.dbo.spt_values v–to get the type of object

ON o.type = SUBSTRING(v.name, 1, 2) COLLATE database_default

AND v.type = ‘O9T’

UNION

SELECT ‘Type’

,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + ‘.’ + name, ‘OwnerId’))

,DB_NAME()+’.’+Schema_name(schema_ID)+’.’+name

FROM sys.types

UNION

SELECT ‘XML Schema Collection’

,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id))

,DB_NAME()+’.’+Schema_name(xsc.schema_ID)+’.’+xsc.name

FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s

ON s.schema_id = xsc.schema_id

)f

where [owner Name] not like ‘sys’

What’s been recently modified then?

If you are working with others on a database, then one of the more useful bits of code you can have is the following, which tells you the date at which your database objects were last-modified. This is the full code, but generally you’ll modify it slightly as you’ll just want to know the twenty latest modifications or so, or maybe list all the objects modified in the past week. Sadly, it will not tell you what has been deleted!

SELECT  [Qualified_Name], Object_Type, CONVERT(CHAR(17), Created, 113),

CONVERT(CHAR(17), Last_Modified, 113)

FROM    (SELECT –firstly, we’ll search the names of the basic objects

DB_NAME()+’.’+Object_Schema_name(s.[object_ID])

+’.’+COALESCE(p.name+’.’, ”)+s.name

AS [Qualified_Name],

REPLACE(SUBSTRING(v.name, 5, 31), ‘cns’, ‘constraint’)+’ name’

AS Object_Type, s.create_date AS ‘Created’,

s.modify_date AS ‘Last_Modified’

FROM   sys.objects S –to get the objects

LEFT OUTER JOIN master.dbo.spt_values v –to get the type of object

ON s.type=SUBSTRING(v.name, 1, 2) COLLATE database_default

AND v.type=’O9T’

LEFT OUTER JOIN sys.objects p –to get any parent object

ON s.parent_Object_ID=p.[object_ID]

WHERE  Object_Schema_name(s.object_ID) NOT LIKE ‘sys%’

UNION ALL –now search the XML schema collection names

SELECT DB_NAME()+’.’+name, ‘XML Schema Collection name’,

create_date AS ‘created’, modify_date AS ‘Last Modified’

FROM   sys.xml_schema_collections

UNION ALL

SELECT DB_NAME()+’.’+name, LOWER(type_desc)  COLLATE database_default,

create_date AS ‘created’, modify_date AS ‘Last Modified’

FROM   sys.triggers

WHERE  parent_class=0–only DDL triggers

UNION ALL –names of CLR assemblies

SELECT DB_NAME()+’.’+name, ‘CLR Assembly’, create_date AS ‘created’,

modify_date AS ‘Last Modified’

FROM   sys.assemblies

UNION ALL –almost done. We do the agent jobs too here

SELECT DISTINCT

‘Agent’+’.’+DB_NAME()+’.’+[name]  COLLATE database_default,

‘Agent Job’, date_created, date_modified

FROM   MSDB.dbo.sysJobs Job

INNER JOIN MSDB.dbo.sysJobSteps Step ON Job.Job_Id=Step.Job_Id

WHERE  Database_name LIKE DB_NAME() COLLATE database_default) objects

ORDER BY Last_Modified DESC

Searching all your Databases

You can use these various routines on all databases, or on a list of databases. You can use undocumented code, of course, but a better approach would be to use yet another system catalog called sys.Databases. You can then execute the code against all databases, collecting the result into a single table. Here is an example:

DECLARE @ii INT, –loop counter

@iiMax INT, –loop counter upper limit

@CurrentDatabase VARCHAR(255), –variable holding name of current database

@command NVARCHAR(2000)–the dynamic command

DECLARE @whatWeSearch TABLE –-the table of all the databases we search

(Database_ID INT IDENTITY(1, 1),

DatabaseName VARCHAR(255)

)

DECLARE @Result TABLE –the result

([Tables Without Primary Keys] VARCHAR(255)

)

INSERT INTO @whatWeSearch (DatabaseName)

SELECT name FROM sys.Databases

WHERE name NOT IN (‘Master’, ‘TempDB’, ‘Model’, ‘MSDB’)

–get all the databases we want to search

SELECT @ii=MIN(Database_ID), @iiMax=MAX(Database_ID) FROM @whatWeSearch

–and do them all one after another

WHILE @ii<=@iiMax

BEGIN

SELECT @CurrentDatabase=QUOTENAME(DatabaseName)

FROM @whatWeSearch WHERE Database_ID=@ii

SET @Command=N’Use ‘+@CurrentDatabase+’

Select DB_NAME()+”.”+Object_Schema_name(t.object_ID)+”.”

+t.name  as [tables without primary keys]

FROM sys.tables t

WHERE OBJECTPROPERTY(object_id,”TableHasPrimaryKey”) = 0

ORDER BY [tables without primary keys]’

INSERT INTO @Result ([Tables Without Primary Keys])

EXEC sp_executesql @Command

SELECT @ii=@ii+1 –and on to the next database

END

SELECT [Tables Without Primary Keys] FROM @Result

Advertisements

About alamzyah
Name : Alamsyah Nick Name : Alamzyah Place of Birth : Jakarta, 04 June 1983 sex : Male Religion : Moslem Region : Jakarta, Indonesia Specialist : IT, Computer mail : alamzyah@gmail.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: