-- 作者：李爱武 law_mail@126.com
-- UPDATE记录：
-- 2020-06-24 增加def from xxx，查看存储过程和函数定义，不支持使用架构
-- 2020-06-26 tables参数增加schema, create_date
-- 2021-04-01 直接创建至master数据库，并设置为系统存储过程，可以在任何数据库直接调用
-- 2021-10-12 增加locks选项
-- 2021-10-25 tables选项中，增加表所在文件组
-- 2021-10-26 增加logins，users，cur_db选项
-- 2022-05-31 若sp_show存在，则先删除
-- 2022-05-31 增加spid选项，以方便查询锁的信息
-- 2022-06-07 在外观方面做了几个修改
-- 2022-06-07 查看存储过程和函数定义，支持使用架构
-- 2022-12-09 把sp_ms_marksystemobject改为sp_MS_marksystemobject 
-- 2022-12-21 增加schemas选项
-- 2022-12-22 增加db grants for xxx等用户权相关选项
-- 2023-05-30 增加version，procedures和functions选项，调整代码格式
-- 2023-06-06 增加此存储过程创建成功的提示信息
-- 尚未解决：
-- 2020-06-24 在Azure SQL Database中查询系统表结构不正常

use master
go
if object_id('sp_show') is not null drop procedure sp_show
go
create procedure sp_show
@arg varchar(50)
as
declare @str as varchar(max)
if @arg = 'databases' 
    SELECT 
        cast(name as varchar(15)) as "databases on this server" 
    FROM sys.databases
else if @arg = 'version' 
    print @@version
else if @arg = 'cur_db' 
    print db_name()
else if @arg = 'tables'
    SELECT
        cast(t.name as varchar(15)) as "name", 
        cast(schema_name(t.schema_id) as varchar(10)) "schema",
        t.create_date,
        cast(filegroup_name(i.data_space_id) as varchar(10)) as fg_name 
    FROM sys.tables t, sys.indexes i
    WHERE t.name = object_name(i.object_id) 
else if @arg = 'db_user'
    print user
else if @arg = 'login'
    print system_user
else if substring(ltrim(rtrim(@arg)), 1, 10) = 'index from'
    SELECT 
           cast(object_name(i.object_id) as varchar(15)) as table_name,                                                                                                                                                                                                                                                                                                                                                    
           cast(i.name as varchar(30)) as index_name,
           cast(i.type_desc as varchar(15)) as type,
           cast(c.name as varchar(15)) as column_name 
    FROM sys.indexes i, sys.columns c, sys.index_columns ic
    WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id AND 
          ic.column_id = c.column_id AND ic.object_id = c.object_id
          AND object_name(i.object_id) = right(@arg,len(@arg) - 11)
else IF @arg = 'logins'
    SELECT cast(name as varchar(34)) as name, 
           cast(type_desc as varchar(13)) as type,
           cast(default_database_name as varchar(10)) as defaut_db,
           create_date
    FROM sys.server_principals 
    WHERE type in('S','U', 'R') 
else IF @arg = 'users'
    SELECT cast(name as varchar(25)) as name, 
           cast(type_desc as varchar(15)) as type, 
           create_date, 
           cast(default_schema_name as varchar(15)) as default_sch 
    FROM sys.database_principals 
else IF @arg = 'procedures'
    SELECT cast(routine_name as varchar) as procedure_name from information_schema.routines
    WHERE routine_type = 'PROCEDURE'
else IF @arg = 'functions'
    SELECT cast(routine_name as varchar) as function_name from information_schema.routines
    WHERE routine_type = 'FUNCTION'
else if substring(ltrim(rtrim(@arg)), 1, 8) = 'def from'
begin
    SELECT 
        @str = definition 
    FROM sys.sql_modules
    -- WHERE object_name(object_id) = right(@arg,len(@arg) - 9)
    WHERE object_id = object_id(right(@arg,len(@arg) - 9))
    print @str
end
else if @arg = 'files'
    SELECT 
        df.file_id, 
        cast(df.name as varchar(15)) as logical_name, 
        cast(df.physical_name as varchar(60)) as physical_name, 
        df.size*8192/1024/1024 as "size(MB)",
        case df.max_size 
            when 0 then 'fixed'
            when -1 then 'unlimitted'
            else cast(df.max_size as varchar(20))
        end as "max_size(8kB pages)",
        case df.is_percent_growth
        when 0 then cast(df.growth*8192/1024/1024 as varchar(10)) + 'MB' 
        when 1 then cast(df.growth as varchar(10)) + '%'
        end as "growth",
        cast(df.type_desc as varchar(10)) as type,
        cast(fg.name as varchar(15)) as filegroup_name
    FROM sys.database_files df LEFT OUTER JOIN sys.filegroups fg
    ON df.data_space_id = fg.data_space_id
else if @arg = 'filegroups'
    SELECT 
        cast(name as varchar(20)) as name, 
        cast(type_desc as varchar(20)) as type, 
        is_default
    FROM sys.filegroups 
else if @arg = 'server grants for all'
    select cast(class_desc as varchar(15)) class, 
           cast(suser_name(grantee_principal_id) as varchar(40)) grantee,
           cast(permission_name as varchar) permission
    from sys.server_permissions
else if @arg = 'server grants for me'
    select cast(class_desc as varchar(15)) class, 
           cast(suser_name(grantee_principal_id) as varchar(40)) grantee,
           cast(permission_name as varchar) permission
    from sys.server_permissions
    where suser_name(grantee_principal_id) = system_user

else if substring(ltrim(rtrim(@arg)), 1, 17) = 'server grants for'
    select cast(class_desc as varchar(15)) class, 
           cast(suser_name(grantee_principal_id) as varchar(40)) grantee,
           cast(permission_name as varchar) permission
    from sys.server_permissions
    where suser_name(grantee_principal_id) = right(ltrim(rtrim(@arg)), len(ltrim(rtrim(@arg))) - 18)

else if ltrim(rtrim(@arg)) = 'db grants for all'
begin
        select cast(user_name(grantee_principal_id) as varchar(10)) as grantee, 
       cast(class_desc as varchar(20)) as class, 
       cast(permission_name as varchar) as permission,
       cast(object_name(major_id) as varchar(20)) as name, 
       'NULL' as column_name
         from sys.database_permissions 
         where class_desc = 'database'
        union
        select cast(user_name(grantee_principal_id) as varchar(10)) as grantee, 
       cast(class_desc as varchar(20)) as class, 
       cast(permission_name as varchar) as permission,
       cast(schema_name(major_id) as varchar(10)) as name, 
       'NULL' as column_name
         from sys.database_permissions 
         where class_desc = 'schema' 
         union
         select cast(user_name(grantee_principal_id) as varchar(10)) as grantee, 
       'OBJECT' as class, 
       cast(permission_name as varchar) as permission,
       cast(schema_name(o.schema_id) + '.' +  object_name(dp.major_id) as varchar(20)) name, 
       'NULL'  as column_name
         from sys.database_permissions dp inner join sys.objects o ON dp.major_id = o.object_id
         where class_desc = 'object_or_column'  and minor_id = 0 
         union
         select  cast(user_name(dp.grantee_principal_id) as varchar(10)) as grantee,
        'COLUMN' as class,
        cast(dp.permission_name as varchar) as permission,
        cast(schema_name(o.schema_id) + '.' +  object_name(dp.major_id) as varchar(20)) name,
        cast(c.name as varchar(15)) as column_name        
        from    sys.database_permissions dp
        INNER JOIN sys.objects O ON dp.major_id = O.object_id
        INNER JOIN sys.columns C ON C.object_id = O.object_id AND C.column_id = dp.minor_id
        INNER JOIN sys.database_principals P ON P.principal_id = dp.grantee_principal_id
end 
else IF @arg = 'db grants for me'
begin
         select cast(user_name(grantee_principal_id) as varchar(10)) as grantee, 
       cast(class_desc as varchar(20)) as class, 
       cast(permission_name as varchar) as permission,
       cast(object_name(major_id) as varchar(20)) as name, 
       'NULL' as column_name
         from sys.database_permissions 
         where user_name(grantee_principal_id) = user  
              and class_desc = 'database'
         union
        select cast(user_name(grantee_principal_id) as varchar(10)) as grantee, 
       cast(class_desc as varchar(20)) as class, 
       cast(permission_name as varchar) as permission,
       cast(schema_name(major_id) as varchar(10)) as name, 
       'NULL' as column_name
         from sys.database_permissions 
         where user_name(grantee_principal_id) = user  
          and class_desc = 'schema' 
         union
         select cast(user_name(grantee_principal_id) as varchar(10)) as grantee, 
       'OBJECT' as class, 
       cast(permission_name as varchar) as permission,
       cast(schema_name(o.schema_id) + '.' +  object_name(dp.major_id) as varchar(20)) name, 
       'NULL'  as column_name
         from sys.database_permissions dp inner join sys.objects o ON dp.major_id = o.object_id
         where user_name(grantee_principal_id) = user  
              and class_desc = 'object_or_column'  and minor_id = 0 
         union
         select  cast(user_name(dp.grantee_principal_id) as varchar(10)) as grantee,
        'COLUMN' as class,
        cast(dp.permission_name as varchar) as permission,
        cast(schema_name(o.schema_id) + '.' +  object_name(dp.major_id) as varchar(20)) name,
        cast(c.name as varchar(15)) as column_name        
        from    sys.database_permissions dp
        INNER JOIN sys.objects O ON dp.major_id = O.object_id
        INNER JOIN sys.columns C ON C.object_id = O.object_id AND C.column_id = dp.minor_id
        INNER JOIN sys.database_principals P ON P.principal_id = dp.grantee_principal_id
                where user_name(dp.grantee_principal_id) = user 
end 

else if substring(ltrim(rtrim(@arg)), 1, 13) = 'db grants for'
begin
         select cast(user_name(grantee_principal_id) as varchar(15)) as grantee, 
       cast(class_desc as varchar(15)) as class, 
       cast(permission_name as varchar(45)) as permission,
       cast(object_name(major_id) as varchar(20)) as name, 
       'NULL' as column_name
         from sys.database_permissions 
         where user_name(grantee_principal_id) = right(ltrim(rtrim(@arg)), len(ltrim(rtrim(@arg))) - 14)  
              and class_desc = 'database'
         union
        select cast(user_name(grantee_principal_id) as varchar(15)) as grantee, 
       cast(class_desc as varchar(15)) as class, 
       cast(permission_name as varchar(45)) as permission,
       cast(schema_name(major_id) as varchar(10)) as name, 
       'NULL' as column_name
         from sys.database_permissions 
         where user_name(grantee_principal_id) = right(ltrim(rtrim(@arg)), len(ltrim(rtrim(@arg))) - 14)  
          and class_desc = 'schema' 
         union
         select cast(user_name(grantee_principal_id) as varchar(15)) as grantee, 
       'OBJECT' as class, 
       cast(permission_name as varchar(45)) as permission,
       cast(schema_name(o.schema_id) + '.' +  object_name(dp.major_id) as varchar(20)) name, 
       'NULL'  as column_name
         from sys.database_permissions dp inner join sys.objects o ON dp.major_id = o.object_id
         where user_name(grantee_principal_id) = right(ltrim(rtrim(@arg)), len(ltrim(rtrim(@arg))) - 14)  
              and class_desc = 'object_or_column'  and minor_id = 0 
         union
         select  cast(user_name(dp.grantee_principal_id) as varchar(15)) as grantee,
        'COLUMN' as class,
        cast(dp.permission_name as varchar(45)) as permission,
        cast(schema_name(o.schema_id) + '.' +  object_name(dp.major_id) as varchar(20)) name,
        cast(c.name as varchar(15)) as column_name        
        from    sys.database_permissions dp
        INNER JOIN sys.objects O ON dp.major_id = O.object_id
        INNER JOIN sys.columns C ON C.object_id = O.object_id AND C.column_id = dp.minor_id
        INNER JOIN sys.database_principals P ON P.principal_id = dp.grantee_principal_id
                where user_name(dp.grantee_principal_id) = right(ltrim(rtrim(@arg)), len(ltrim(rtrim(@arg))) - 14) 
end
else if substring(ltrim(rtrim(@arg)), 1, 12) = 'columns from'
    begin
    SELECT 
        cast(column_name as varchar) as "COLUMN",
        cast(type as varchar(15)) as "TYPE",
        NULLABLE
    FROM
    (
        SELECT 
            c.column_id as column_id,
            c.name as column_name,
            case t.name
                when 'numeric' then t.name+'('+cast(c.precision as varchar(5))+','+ cast(c.scale as varchar(5))+')'
                when 'char' then t.name+'('+cast(c.max_length as varchar(5))+')'
                when 'varchar' then t.name+'('+cast(c.max_length as varchar(5))+')'
                when 'nvarchar' then t.name+'('+cast(c.max_length as varchar(5))+')'
                else t.name
            end as type,
            case c.is_nullable 
                when 1 then 'Y'
                else 'N'
            end as NULLABLE
        FROM sys.all_columns as c, sys.types as t
        WHERE c.system_type_id = t.system_type_id
            AND t.name<>'sysname' 
            AND c.object_id = object_id(right(@arg,len(@arg) - 13))
            --AND object_name(c.object_id) = '''' + right('columns from emp',len('columns from emp') - 13) + ''''
    ) as a
    ORDER BY column_id
    end
else if @arg = 'locks'
    select cast(request_session_id as varchar(4)) as spid,
           case when resource_type='OBJECT' then
                   cast(object_name(resource_associated_entity_id) as varchar(10))
                   when resource_associated_entity_id=0 then 'n/a'
                else cast(object_name(p.object_id) as varchar(10))
           end as ety_name,
           index_id,
           cast(resource_type as varchar(8)) as ety_type,
           cast(resource_description as varchar(15)) as description,
           cast(request_mode as varchar(10)) as mode,
           cast(request_status as varchar(8)) as status
    from sys.dm_tran_locks t left join sys.partitions p
         on p.hobt_id=t.resource_associated_entity_id
    where resource_database_id=db_id()     
    --and ety_type!= 'DATABASE'
else if @arg = 'spid'
     print @@spid
else if @arg = 'schemas'
    select cast(name as varchar) as schema_name from sys.schemas
else if @arg = 'triggers'
        select cast(name as varchar(25)) as name, cast(object_name(parent_id) as varchar(15)) table_name
       from sys.triggers
else if @arg = 'help' 
    begin
        PRINT 'The argument should be:' 
        PRINT '======================================='
        PRINT 'databases' 
        PRINT 'version'
        PRINT 'cur_db'
        PRINT 'schemas'
        PRINT 'tables'
        PRINT 'procedures'
        PRINT 'functions'
        PRINT 'db_user'
        PRINT 'locks'
        PRINT 'login'
        PRINT 'logins'
        PRINT 'users'
        PRINT 'spid'
        PRINT 'triggers'
        PRINT 'files' 
        PRINT 'filegroups'
        PRINT '''' + 'columns from tb_name' + '''' 
        PRINT '''' + 'index from tb_name' + '''' 
        PRINT '''' + 'def from module_name' + '''' 
        PRINT '''' + 'server grants for all' + ''''   
        PRINT '''' + 'server grants for login_name' + ''''  
        PRINT '''' + 'db grants for me' + ''''  
        PRINT '''' + 'db grants for all' + '''' 
        PRINT '''' + 'db grants for dbuser_name' + ''''
        PRINT '2023-06-06'
    end
else   
    begin
        PRINT 'The argument should be:' 
        PRINT '======================================='
        PRINT 'databases' 
        PRINT 'version' 
        PRINT 'cur_db'
        PRINT 'schemas'
        PRINT 'tables'
        PRINT 'procedures'
        PRINT 'functions'
        PRINT 'db_user'
        PRINT 'locks'
        PRINT 'login'
        PRINT 'logins'
        PRINT 'users'
        PRINT 'spid'
        PRINT 'triggers'
        PRINT 'files' 
        PRINT 'filegroups'
        PRINT '''' + 'columns from tb_name' + '''' 
        PRINT '''' + 'index from tb_name' + '''' 
        PRINT '''' + 'def from module_name' + '''' 
        PRINT '''' + 'server grants for all' + ''''   
        PRINT '''' + 'server grants for login_name' + ''''  
        PRINT '''' + 'db grants for me' + ''''  
        PRINT '''' + 'db grants for all' + '''' 
        PRINT '''' + 'db grants for dbuser_name' + ''''
        PRINT '2023-06-06'
    end
go
sp_MS_marksystemobject 'sp_show'
grant execute on master.dbo.sp_show to public
print 'sp_show created successfully.'
go