SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script to get an ordered list of all stored procedures with parameters


Script to get an ordered list of all stored procedures with parameters

Author
Message
derek.colley
derek.colley
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4304 Visits: 603
Comments posted to this topic are about the item Script to get an ordered list of all stored procedures with parameters

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Steve Shurts
Steve Shurts
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 52
Looks like a very handy piece of code; however, the first thing I did was add the following:

SELECT * FROM @procedureListTable WHERE sch_name <> 'sys'

which eliminates all of the system generated procs from the listing...
derek.colley
derek.colley
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4304 Visits: 603
Well spotted!

In response to an email asking some questions about the script I posted, some more related scripts...


The first is short and enables you to create a dictionary of the table objects for a particular database, together with all columns, the columns' datatypes and nullability status.


SELECT schemata.name [schema] , tables.name [table], cols.name [column_name],
types.name [column_datatype], cols.length [column_length]
FROM sys.schemas schemata
INNER JOIN sys.tables tables ON schemata.schema_id = tables.schema_id
INNER JOIN sysobjects objs ON tables.name = objs.name
LEFT JOIN syscolumns cols ON objs.id = cols.id
LEFT JOIN systypes types ON cols.xtype = types.xtype
ORDER BY schemata.name ASC, tables.name ASC , cols.name ASC



If you'd like to run this for every database, simply create an output table like this:


CREATE TABLE dbo.DataDictionary (
[RecordID] BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[database_name] VARCHAR(50), [schema] VARCHAR(50) , [table] VARCHAR(100),
[column_name] VARCHAR(100), [column_datatype] VARCHAR(50), [column_length] BIGINT )

EXEC sp_msforeachdb '
INSERT INTO dbo.DataDictionary ( [database_name], [schema], [table], [column_name], [column_datatype], [column_length] )
SELECT ''?'' [database_name], schemata.name [schema] , tables.name [table], cols.name [column_name],
types.name [column_datatype], cols.length [column_length]
FROM sys.schemas schemata
INNER JOIN sys.tables tables ON schemata.schema_id = tables.schema_id
INNER JOIN sysobjects objs ON tables.name = objs.name
LEFT JOIN syscolumns cols ON objs.id = cols.id
LEFT JOIN systypes types ON cols.xtype = types.xtype
ORDER BY schemata.name ASC, tables.name ASC , cols.name ASC
'



Then you'll be able to query the data dictionary by database name too.

You can get the server principals, server roles, and their associated database principals and database roles by using the sys.server_principals and sys.database_principals system views. You can map their connections using the SID (note this might backfire for orphaned users or if users have been imported from another system).


SELECT sp.name, sp.type_desc, sp.default_database_name,
dp.name, dp.type_desc, default_schema_name
FROM sys.server_principals sp
LEFT JOIN sys.database_principals dp ON sp.sid = dp.sid
-- WHERE sp.is_fixed_role = 0 AND dp.is_fixed_role = 0 -- feel free to leave this out
UNION
SELECT sp.name, sp.type_desc, sp.default_database_name,
dp.name, dp.type_desc, default_schema_name
FROM sys.server_principals sp
RIGHT JOIN sys.database_principals dp ON sp.sid = dp.sid
--WHERE sp.is_fixed_role = 0 AND dp.is_fixed_role = 0 -- feel free to leave this out



There are plenty of interesting columns in the sys.server_principals and sys.database_principals views - feel free to add and remove these as you see fit.

You could also use these queries in Excel or SSRS to create a manager-friendly report or easily referencable offline data dictionary.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Tee Time
Tee Time
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1841 Visits: 465
Thanks Derek, good stuff!
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36562 Visits: 886
Thanks for the script.
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36562 Visits: 886
...oh and the extra stuff too.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search