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]