Post reply

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

  • August 8, 2012 at 8:48 pm

    #279350

    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 https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • August 14, 2012 at 10:23 am

    #1525640

    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...

  • August 15, 2012 at 12:42 pm

    #1526166

    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

    , 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) ,

    VARCHAR(100),

    [column_name] VARCHAR(100), [column_datatype] VARCHAR(50), [column_length] BIGINT )

    EXEC sp_msforeachdb '

    INSERT INTO dbo.DataDictionary ( [database_name], [schema],

    , [column_name], [column_datatype], [column_length] )

    SELECT ''?'' [database_name], schemata.name [schema] , tables.name

    , 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 https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • August 15, 2012 at 3:01 pm

    #1526241

    Thanks Derek, good stuff!

  • May 10, 2016 at 6:52 am

    #1877745

    Thanks for the script.

  • May 10, 2016 at 6:52 am

    #1877746

    ...oh and the extra stuff too.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply