SQL Server DMVs

  • I need to query SQL logins and their Server Roles, Securables, and Database Roles. What are the SQL Server 2008 (non-R2) DMVs that I need to query to get this information?

    Thanks.

  • Try this link

    In the first article you should see a list of all the current DMV's broken down by category

    All joking aside, you can use this TSQL (snagged from my co-worker), the procedure will generate a fancy HTML output of all the information. Save that off to an HTML file and open it up for a very snazzy representation of what you're looking for (at least what I think it is you're looking for)

    CREATE PROCEDURE [dbo].[dba_AuditUsersPermissions]

    @WithPublicchar(1) = 'N'

    AS

    -- EXEC F1Settings.dbo.dba_AuditUsersPermissions

    /* ######################################### START MAIN PROCEDURE HERE ########################################## */

    SET NOCOUNT ON

    DECLARE @sqlvarchar(MAX)

    DECLARE @strHTMLvarchar(MAX)

    DECLARE @iint

    DECLARE @rcint

    DECLARE @dbnamevarchar(400)

    DECLARE @Printvarchar(MAX)

    DECLARE @CMDnvarchar(MAX)

    --Create temp tables

    IF OBJECT_ID(N'tempdb.dbo.#syslogins') IS NOT NULL

    BEGIN

    DROP TABLE #syslogins

    END

    CREATE TABLE #syslogins

    (

    RowNumberint,

    Namesysname,

    DBNamesysname,

    [Language]varchar(25),

    IsDeniednvarchar(20),

    IsWinAuthentication varchar(20),

    IsWinGroupvarchar(20),

    CreateDatedatetime,

    UpdateDatedatetime,

    ServerRolesvarchar(MAX)

    )

    IF OBJECT_ID(N'tempdb.dbo.#LoginMap') IS NOT NULL

    BEGIN

    DROP TABLE #LoginMap

    END

    CREATE TABLE #LoginMap

    (

    LoginNamevarchar(200),

    UserNamevarchar(200) NULL

    )

    IF OBJECT_ID(N'tempdb.dbo.#RoleUser') IS NOT NULL

    BEGIN

    DROP TABLE #RoleUser

    END

    CREATE TABLE #RoleUser

    (

    RoleName varchar(200),

    UserName varchar(200) NULL

    )

    IF OBJECT_ID(N'tempdb.dbo.#ObjectPerms') IS NOT NULL

    BEGIN

    DROP TABLE #ObjectPerms

    END

    CREATE TABLE #ObjectPerms

    (

    RowNumberint IDENTITY,

    UserNamevarchar(50),

    PerTypevarchar(10),

    PermNamevarchar(30),

    SchemaNamevarchar(50),

    ObjectNamevarchar(100),

    ObjectTypevarchar(20),

    ColNamevarchar(50),

    IsGrantOptionvarchar(10)

    )

    IF OBJECT_ID(N'tempdb.dbo.#DatabasePerms') IS NOT NULL

    BEGIN

    DROP TABLE #DatabasePerms

    END

    CREATE TABLE #DatabasePerms

    (

    RowNumberint IDENTITY,

    UserNamevarchar(50),

    PermTypevarchar(20),

    PermNamevarchar(50),

    IsGrantOptionvarchar(5)

    )

    -----------------Print header of the report--------------------

    SELECT @strHTML = '<HTML><HEAD><TITLE> SECURITY AUDIT </TITLE><STYLE>TD.Sub{FONT-WEIGHT:bold;BORDER-BOTTOM: '

    + '0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: '

    + '0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} BODY{FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} '

    + 'TABLE{BORDER-BOTTOM: 1pt solid #000000;BORDER-LEFT: 0pt solid #000000;BORDER-RIGHT: 1pt solid #000000;BORDER-TOP: '

    + '0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD{BORDER-BOTTOM: '

    + '0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; '

    + 'FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD.Title{FONT-WEIGHT:bold;BORDER-BOTTOM: '

    + '0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; '

    + 'FONT-FAMILY: Tahoma;FONT-SIZE: 12pt} A.Index{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#000099;'

    + 'FONT-FAMILY:Tahoma;TEXT-DECORATION:none} A.Index:HOVER{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#990000;'

    + 'FONT-FAMILY:Tahoma;TEXT-DECORATION:none}</STYLE></HEAD><BODY><A NAME="_top"></A>

    '

    PRINT @strHTML

    -----------------Login information-------------------------------------------------------------

    INSERT INTO #syslogins

    SELECT

    ROW_NUMBER () OVER (ORDER BY name) AS RowNumber,

    Name,

    DBName,

    [Language],

    CONVERT(char(10), CASE denylogin WHEN 1 THEN 'X' ELSE '' END) AS IsDenied,

    CONVERT(char(10), CASE isntname WHEN 1 THEN 'X' ELSE '' END) AS IsWinAuTHENtication,

    CONVERT(char(10), CASE isntgroup WHEN 1 THEN 'X' ELSE '' END) AS IsWinGroup,

    CreateDate,

    UpdateDate,

    CONVERT(varchar(2000),

    CASE sysadmin WHEN 1 THEN 'sysadmin,'ELSE '' END

    + CASE securityadmin WHEN 1 THEN 'securityadmin,'ELSE '' END

    + CASE serveradmin WHEN 1 THEN 'serveradmin,'ELSE '' END

    + CASE setupadmin WHEN 1 THEN 'setupadmin,'ELSE '' END

    + CASE processadmin WHEN 1 THEN 'processadmin,'ELSE '' END

    + CASE diskadmin WHEN 1 THEN 'diskadmin,'ELSE '' END

    + CASE dbcreator WHEN 1 THEN 'dbcreator,'ELSE '' END

    + CASE bulkadmin WHEN 1 THEN 'bulkadmin'ELSE '' END ) AS ServerRoles

    FROM master..syslogins WITH(READUNCOMMITTED)

    ORDER BY name

    SET @rc = @@ROWCOUNT

    SELECT @strHTML = '

    <CENTER><FONT SIZE="5"><B> Server ' + @@SERVERNAME + '</B></FONT></CENTER>

    '

    PRINT @strHTML

    PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">'

    -- Query the data only if there are rows:

    IF @rc = 0

    BEGIN --rc=0

    SELECT @strHTML = '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_LoginInfomration">'

    + 'Logins information</A></B> </TD></TR>'

    PRINT @strHTML

    SELECT @strHTML = '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%">'

    + '<B>There are no logins on this server</B> </TD></TR>'

    PRINT @strHTML

    END

    ELSE

    BEGIN

    UPDATE #syslogins

    SET ServerRoles = SUBSTRING(ServerRoles, 1, LEN(ServerRoles) -1)

    WHERE SUBSTRING(ServerRoles, LEN(ServerRoles), 1) = ','

    UPDATE #syslogins

    SET ServerRoles = ''

    WHERE LTRIM(RTRIM(ServerRoles)) = ''

    SELECT @strHTML = '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" '

    + 'BORDERCOLOUR="003366" WIDTH="100%">'

    PRINT @strHTML

    SELECT @strHTML = '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="9" ALIGN="center"><B>'

    + '<A NAME="_LoginInfomration">Logins information</A></B> </TD></TR>'

    PRINT @strHTML

    SELECT @strHTML = '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="50%"><B>Login Name</B> </TD>'

    + '<TD ALIGN="left" WIDTH="50%"><B>Default DB</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Language</B> '

    + '</TD><TD ALIGN="left" WIDTH="70%"><B>Denied acess?</B> </TD><TD ALIGN="left" WIDTH="70%">'

    + '<B>Windows Auth?</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Window group?</B> </TD>'

    + '<TD ALIGN="left" WIDTH="70%"><B>Date created</B> </TD><TD ALIGN="left" WIDTH="70%">'

    + '<B>Date UPDATEd</B> </TD><TD AALIGN="left" WIDTH="770%"><B>Server roles</B> </TD></TR>'

    PRINT @strHTML

    SET @i = 1

    WHILE @i <= @rc

    BEGIN

    SELECT @strHTML =

    '<TR><TD><B>' + CONVERT(varchar(50),name) + '</B> </TD>'

    + '<TD>' + CONVERT(varchar(50),CASE ISNULL(dbname,'') WHEN '' THEN '--' ELSE ISNULL(dbname,'--') END)

    + ' </TD>' + '<TD>' + CONVERT(varchar(50),ISNULL(language,'')) + ' </TD>'

    + '<TD><center>' + CONVERT(varchar(10),ISNULL(IsDenied,'')) + ' </TD>'

    + '<TD><center>' + CONVERT(varchar(10),ISNULL(IsWinAuTHENtication,'')) + ' </TD>'

    + '<TD><center>' + CONVERT(varchar(10),ISNULL(IsWinGroup,'')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(50),ISNULL(createdate,'')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(50),ISNULL(UPDATEdate,'')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(100),ISNULL(ServerRoles,'')) + ' </TD>'

    + '</TR>'

    FROM #syslogins

    WHERE RowNumber = @i

    PRINT @strHTML

    SET @i = @i + 1

    END

    PRINT '</TABLE></DIV>

    <A CLASS="Index" HREF="#_top">Back To Top ^</A>

    '

    PRINT'

    <CENTER></CENTER>

    '

    END --rc=0

    ---------------Fetch data per database-------------------------------------------------

    DECLARE dbs CURSOR FOR

    SELECT name

    FROM master..sysdatabases

    WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'

    ORDER BY name

    OPEN dbs

    FETCH NEXT FROM dbs INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    TRUNCATE TABLE #LoginMap

    TRUNCATE TABLE #RoleUser

    TRUNCATE TABLE #ObjectPerms

    TRUNCATE TABLE #DatabasePerms

    SELECT @strHTML = '

    <CENTER><FONT SIZE="5"><B> Database ' + @dbname + '</B></FONT></CENTER>

    '

    PRINT @strHTML

    -----------------Mapping of logins to users------------------

    SET @CMD = 'INSERT INTO #LoginMap

    SELECT

    l.loginname,

    u.name

    FROM [' + @dbname + ']..sysusers u

    INNER JOIN [master]..[syslogins] l

    ON u.[sid] = l.[sid]

    WHERE u.uid < 16382

    AND u.name NOT IN (''public'',''dbo'',''guest'')'

    EXEC sp_executesql @CMD

    SET @strHTML = ''

    PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'

    --Query the data only if there are rows

    IF NOT EXISTS (SELECT 1 FROM #LoginMap)

    BEGIN

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_LoginMapping">'

    + 'Mapping of logins to users</A></B> </TD></TR>'

    PRINT @strHTML

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no mappings in this database</B> '

    + '</TD></TR>'

    PRINT @strHTML

    END

    ELSE

    BEGIN

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="center"><B><A NAME="_LoginMapping">'

    + 'Mapping of logins to users</A></B> </TD></TR>'

    PRINT @strHTML

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>Login Name</B> '

    + '</TD><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD></TR>'

    PRINT @strHTML

    SELECT @strHTML = @strHTML +

    '<TR><TD><B>'

    + CONVERT(varchar(50), LoginName)

    + '</B> </TD><TD>'

    + CONVERT(varchar(50), ISNULL(UserName, ''))

    + ' </TD></TR>'

    + char(10)

    FROM #LoginMap

    ORDER BY LoginName

    PRINT @strHTML

    END

    PRINT '</TABLE></DIV>

    <A CLASS="Index" HREF="#_top">Back To Top ^</A>

    '

    ----------------SQL roles per user------------------

    SET @CMD = 'INSERT INTO #RoleUser

    SELECT

    b.name AS Role_name,

    a.name AS User_name '

    + 'FROM ['+ @dbname+']..sysusers a '

    + 'INNER JOIN ['+ @dbname+ ']..sysmembers c

    ON a.uid = c.memberuid '

    + 'INNER JOIN ['+ @dbname+ ']..sysusers b

    ON c.groupuid = b.uid '

    + 'WHERE a.name <> ''dbo'''

    EXEC sp_executesql @CMD

    SET @strHTML = ''

    PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'

    -- Query the data only if there are rows:

    IF NOT EXISTS(SELECT 1 FROM #RoleUser)

    BEGIN

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_DBRoleMapping">'

    + 'Roles per user</A></B> </TD></TR>'

    PRINT @strHTML

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no users mapped to roles '

    + 'in this database</B> </TD></TR>'

    PRINT @strHTML

    END

    ELSE

    BEGIN

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="center"><B><A NAME="_DBRoleMapping">'

    + 'Roles per user</A></B> </TD></TR>'

    PRINT @strHTML

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>Role Name</B> </TD><TD ALIGN="left" WIDTH="70%">'

    + '<B>User Name</B> </TD></TR>'

    PRINT @strHTML

    SELECT @strHTML = @strHTML +

    '<TR><TD><B>'

    + CONVERT(varchar(50), RoleName)

    + '</B> </TD><TD>'

    + CONVERT(varchar(50), ISNULL(UserName, ''))

    + ' </TD></TR>'

    + char(10)

    FROM #RoleUser

    ORDER BY RoleName

    PRINT @strHTML

    END

    PRINT '</TABLE></DIV>

    <A CLASS="Index" HREF="#_top">Back To Top ^</A>

    '

    ----------------Database level Permissions-------------------------

    SET @CMD = 'INSERT INTO #DatabasePerms

    (

    UserName,

    PermType,

    PermName,

    IsGrantOption

    )

    SELECT

    usr.name,

    CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END,

    perm.permission_name,

    CASE WHEN perm.state != ''W'' THEN '''' ELSE ''X'' END AS IsGrantOption

    FROM [' + @dbname + '].sys.database_permissions AS perm

    INNER JOIN [' + @dbname + '].sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

    WHERE perm.major_id = 0

    ORDER BY

    usr.name,

    perm.permission_name ASC,

    perm.state_desc ASC'

    EXEC sp_executesql @CMD

    SET @rc = @@rowcount

    PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'

    -- Query the data only if there are rows:

    IF NOT EXISTS(SELECT 1 FROM #DatabasePerms)

    BEGIN

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_DBLvlPerms">'

    + 'Database level permissions</A></B> </TD></TR>'

    PRINT @strHTML

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no specific permissions on the '

    + 'database level</B></TD></TR>'

    PRINT @strHTML

    END

    ELSE

    BEGIN

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN=" 4" ALIGN="center"><B><A NAME="_DBPObjPerms">'

    + 'Database level permissions</A></B> </TD></TR>'

    PRINT @strHTML

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD><TD ALIGN="left" WIDTH="70%">'

    + '<B>Permission type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission Name</B> </TD>'

    + '<TD ALIGN="left" WIDTH="70%"><B>Grant option?</B> </TD></TR>'

    PRINT @strHTML

    SET @i = 1

    WHILE @i <= @rc

    BEGIN

    SELECT @strHTML =

    '<TR><TD><B>' + CONVERT(varchar(50), UserName) + '</B> </TD>'

    + '<TD>' + CONVERT(varchar(50), ISNULL(PermType, '')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(50), ISNULL(PermName, '')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(5), ISNULL(IsGrantOption, '')) + ' </TD>'

    + '</TR>'

    FROM #DatabasePerms

    WHERE Rownumber = @i

    PRINT @strHTML

    SET @i = @i + 1

    END

    END

    PRINT '</TABLE></DIV>

    <A CLASS="Index" HREF="#_top">Back To Top ^</A>

    '

    ----------------Database object Permissions-------------------------

    SET @CMD = 'INSERT INTO #ObjectPerms

    (

    UserName,

    PerType,

    PermName,

    SchemaName,

    ObjectName,

    ObjectType,

    ColName,

    IsGrantOption

    )

    SELECT

    usr.name AS UserName,

    CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END AS PerType,

    perm.permission_name,

    USER_NAME(obj.schema_id) AS SchemaName,

    obj.name AS ObjectName,

    CASE obj.Type

    WHEN ''U'' THEN ''Table''

    WHEN ''V'' THEN ''View''

    WHEN ''P'' THEN ''Stored Proc''

    WHEN ''FN'' THEN ''Function''

    ELSE obj.Type

    END AS ObjectType,

    CASE WHEN cl.column_id IS NULL THEN ''--'' ELSE cl.name END AS ColName,

    CASE WHEN perm.state = ''W'' THEN ''X'' ELSE ''--'' END AS IsGrantOption

    FROM [' + @dbname + '].sys.database_permissions AS perm

    INNER JOIN [' + @dbname + '].sys.objects AS obj

    ON perm.major_id = obj.[object_id]

    INNER JOIN [' + @dbname + '].sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id '

    IF @WithPublic = 'N'

    SET @CMD = @CMD + '

    AND usr.name <> ''public'''

    SET @CMD = @CMD + '

    LEFT JOIN [' + @dbname + '].sys.columns AS cl

    ON cl.column_id = perm.minor_id

    AND cl.[object_id] = perm.major_id

    WHERE obj.Type <> ''S''

    ORDER BY usr.name, perm.state_desc ASC, perm.permission_name ASC'

    EXEC sp_executesql @CMD

    SET @rc = @@rowcount

    PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'

    -- Query the data only if there are rows:

    IF NOT EXISTS(SELECT 1 FROM #ObjectPerms)

    BEGIN

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_DBPObjPerms">'

    + 'Object permissions</A></B> </TD></TR>'

    PRINT @strHTML

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no specific permissions to objects in '

    + 'this database</B> </TD></TR>'

    PRINT @strHTML

    END

    ELSE

    BEGIN

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="8" ALIGN="center"><B><A NAME="_DBPObjPerms">'

    + 'Object permissions</A></B> </TD></TR>'

    PRINT @strHTML

    SET @strHTML = '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD><TD ALIGN="left" WIDTH="70%">'

    + '<B>Permission type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission Name</B> </TD>'

    + '<TD ALIGN="left" WIDTH="70%"><B>Schema Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Object Name</B> '

    + '</TD><TD ALIGN="left" WIDTH="70%"><B>Object type type</B> </TD><TD ALIGN="left" WIDTH="70%">'

    + '<B>Column Name</B> </TD><TD ALIGN=" left" WIDTH="70%"><B>Grant option?</B> </TD></TR>'

    PRINT @strHTML

    SET @i = 1

    WHILE @i <= @rc

    BEGIN

    SELECT @strHTML =

    '<TR><TD><B>' + CONVERT(varchar(50),UserName) + '</B> </TD>'

    + '<TD>' + CONVERT(varchar(50), ISNULL(PerType, '')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(50), ISNULL(PermName, '')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(50), ISNULL(SchemaName, '')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(50), ISNULL(ObjectName, '')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(30), ISNULL(ObjectType, '')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(50), ISNULL(ColName, '')) + ' </TD>'

    + '<TD>' + CONVERT(varchar(5), ISNULL(IsGrantOption, '')) + ' </TD></TR>'

    FROM #ObjectPerms

    WHERE Rownumber = @i

    PRINT @strHTML

    SET @i = @i + 1

    END

    END

    PRINT '</TABLE></DIV>

    <A CLASS="Index" HREF="#_top">Back To Top ^</A>

    '

    FETCH NEXT FROM dbs INTO @dbname

    END

    ---------------Close cursor and drop all temporary objects-------------

    CLOSE dbs

    DEALLOCATE dbs

    PRINT '</BODY></HTML>'

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Thank you!

    That's exactly what I was looking for!

    Much Grass!

    :w00t:

  • Awesome, glad I was able to help. The HTML output is nice for audits too 🙂

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • I have an amazingly similar HTML report for all objects in a given database.

    here's a link to teh sql file, it's kind of long, but creates similar output;

    it's based off of a script contribution here on SSC, which I modified just a little.

    db_documentation_enhanced.txt

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks, Lowell. I'll check it out.

  • Lowell, I have no doubt that the origination of the SQL I posted was derived from this website 🙂

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie (11/30/2011)


    Lowell, I have no doubt that the origination of the SQL I posted was derived from this website 🙂

    I've been haunting this place a while, and i never tripped over your example yet; I added it to my snippets right away;

    I wonder if the same contributor built the models for them both 🙂

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 8 posts - 1 through 7 (of 7 total)

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