home
training courses
why choose us?
solutions
support
company
LearnItFirst User Forum
Welcome Guest
Search
|
New Posts
|
Members
|
Log In
|
Register
SQL Server Forum - LearnItFirst.com
»
SQL Server Scripts, Code Samples and SSMS Custom Reports
»
SQL Server 2005/2008
»
Script to Get All Objects in a Schema
Script to Get All Objects in a Schema
Options
Previous Topic
·
Next Topic
Scott Whigham
Posted:
Saturday, July 28, 2007 7:47:07 AM
Rank: Super Mod
Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX
I wrote this last week to help me locate all of the objects in a specified schema. It is helpful on a project I'm working on that currently has eight schemas and sometimes it's difficult to remember which schema contains which objects. It uses a Split function based on Erland Sommerskog's split info (link below) so thanks, Erland
Code:
CREATE TABLE dbo.SequenceNumbers(SeqNo int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SequenceNumbers PRIMARY KEY)
GO
DECLARE @i INT
SET @i = 0
WHILE @I<8000
BEGIN
INSERT SequenceNumbers DEFAULT VALUES
SET @i = @i + 1
END
GO
CREATE FUNCTION dbo.Split (
@Array VARCHAR(MAX)
, @Delimiter CHAR(1) = ','
)
RETURNS TABLE
AS
/*
Basically a split function that works against an array. This is from Erland Sommarskog --
http://www.sommarskog.se/arrays-in-sql.html
*/
RETURN (
SELECT SeqNo - LEN(REPLACE(LEFT(@Array, SeqNo), ',', '')) + 1 AS Position, LTRIM(SUBSTRING(@Array, SeqNo, CHARINDEX(',', @Array + ',', SeqNo) - SeqNo)) AS Element
FROM dbo.SequenceNumbers
WHERE SeqNo <= LEN(@Array) AND SUBSTRING(',' + @Array, SeqNo, 1) = @Delimiter
)
GO
CREATE SCHEMA dba
GO
CREATE PROC dba.GetObjectsInSchema (
@SchemaName SYSNAME
, @ObjectType CHAR(2) = NULL
, @ObjectTypesToExclude NVARCHAR(128) = 'PK, UQ, C, D, F' -- array of object types to exclude; default to leaving out the column-level objects
)
AS
IF @ObjectType IS NULL
SELECT SCHEMA_NAME(schema_id) AS SchemaName, name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) = @SchemaName
AND Type NOT IN (SELECT Element COLLATE Latin1_General_CI_AS_KS_WS FROM dbo.Split(@ObjectTypesToExclude, ','))
ORDER BY name, type
ELSE
SELECT SCHEMA_NAME(schema_id) AS SchemaName, name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) = @SchemaName
AND type=@ObjectType
AND Type NOT IN (SELECT Element COLLATE Latin1_General_CI_AS_KS_WS FROM dbo.Split(@ObjectTypesToExclude, ','))
ORDER BY name
Some example use:
EXEC dba.GetObjectsInschema @SchemaName = ‘dbo’ – returns all objects in the dbo schema
EXEC dba.GetObjectsInschema @SchemaName = ‘dbo’, @ObjectType = ‘V’ – returns only views in the dbo schema
EXEC dba.GetObjectsInschema @SchemaName = ‘dbo’, @ObjectsToExclue = ‘P, FK, UQ, U’ – returns all objects in the dbo schema except primary keys, foreign keys, unique constraints and tables
NOTE: This has three supporting objects included – a new schema (“dba”), a sequence numbers table, and a split function.
Back to top
Users browsing this topic
Guest
Forum Jump
SQL Server Database Administration
- General SQL Database Question & Answer
- Backup, Recovery and Disaster Recovery
- SQL Server Security
- Integration Services (SSIS) and DTS
Transact-SQL Programming
- DML (SELECT, INSERT, UPDATE, DELETE) Questions
- Stored Procedures, Triggers, & Functions
SQL Server Scripts, Code Samples and SSMS Custom Reports
- All SQL Server Versions
- SQL Server 2005/2008
- SQL Server Management Studio Custom Reports
Customer Service
- Video Requests
You
cannot
post new topics in this forum.
You
cannot
reply to topics in this forum.
You
cannot
delete your posts in this forum.
You
cannot
edit your posts in this forum.
You
cannot
create polls in this forum.
You
cannot
vote in polls in this forum.
SQL Server 2005 DBA Training Videos
SQL Server 2008 DBA Training Videos
Email this topic
RSS Feed
Watch this topic
Print this topic
Normal
Threaded