SQL Server 中有用的系统存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/578454/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Useful system stored procedures in SQL Server
提问by DotnetDude
I recently discovered that I could use the sp_helpto get a table definition and have been hooked onto it since then. Before my discovery, I had to open up the Object explorer in SQL Management studio, manually search for the table name, right click on the table and select Design. That was a lot of effort!
我最近发现我可以使用sp_help来获取表定义，并且从那时起就一直使用它。在我发现之前，我不得不在 SQL Management Studio 中打开对象资源管理器，手动搜索表名，右键单击表并选择设计。真是费了好大劲啊！
What other system stored procedures do you all use that you can't simply live without?
你们都使用了哪些其他系统存储过程，您离不开它？
采纳答案by Gulzar Nazim
回答by SQLMenace
All of these undocumented ones
所有这些无证的
xp_getnetname
xp_fileexist
xp_dirtree
xp_subdirs
sp_who2
xp_getfiledetails
xp_fixeddrives
Sp_tempdbspace
xp_enumdsn
xp_enumerrorlogs
sp_MSforeachtable
sp_MSforeachDB
See here: Undocumented stored procedures
请参阅此处：未记录的存储过程
And now since SQl Server 2005 all the Dynamic Management Views like sys.dm_db_index_usage_stats
现在自 SQl Server 2005 起所有动态管理视图，如sys.dm_db_index_usage_stats
回答by MikeW
sp_ helpindex [table] - shows you index info (same info as sp_help)
sp_ helpindex [table] - 显示索引信息（与 sp_help 相同的信息）
sp_helpconstraint [table] - shows you primary/foreign key/defaults and other constraints *
sp_helpconstraint [table] - 显示主键/外键/默认值和其他约束*
sp_depends [obj] - shows dependencies of an object, for example:
sp_depends [obj] - 显示对象的依赖关系，例如：
sp_depends [table] - shows you what stored procs, views, triggers, UDF affect this table
sp_depends [table] - 显示哪些存储过程、视图、触发器、UDF 会影响该表
sp_depends [sproc] - shows what tables etc are affected/used by this stored proc
sp_depends [sproc] - 显示哪些表等受到此存储过程的影响/使用
回答by Dinesh Manne
You can use sp_spaceusedto determine the size of a table or the entire database. If you pass the table name, it returns the space used for that table, when called with no argument it gives the space of the database.
您可以使用它sp_spaceused来确定一个表或整个数据库的大小。如果传递表名，它将返回用于该表的空间，当不带参数调用时，它会提供数据库的空间。
回答by Russ Cam
回答by Andrew Hare
sp_who/sp_who2- lets you know who is doing what on the server.
sp_who/sp_who2- 让您知道谁在服务器上做什么。
回答by Sreekesh O S
sp_catalogs
sp_column_privileges
sp_column_privileges_ex
sp_columns
sp_columns_ex
sp_databases
sp_cursor
sp_cursorclose
sp_cursorexecute
sp_cursorfetch
sp_cursoroption
sp_cursoropen
sp_cursorprepare
sp_cursorprepexec
sp_cursorunprepare
sp_execute
sp_datatype_info
sp_fkeys
sp_foreignkeys
sp_indexes
sp_pkeys
sp_primarykeys
sp_prepare
sp_prepexec
sp_prepexecrpc
sp_unprepare
sp_server_info
sp_special_columns
sp_sproc_columns
sp_statistics
sp_table_privileges
sp_table_privileges_ex
sp_tables
sp_tables_ex
Check This link also
也检查这个链接
回答by Otis
master.dbo.xp_cmdshell
master.dbo.xp_cmdshell
I can't list the number of times I didn't have RDP access to a box but did have a SQL login with sufficient permissions to execute that in order to run shell commands on it.
我无法列出我没有 RDP 访问权限但确实有一个 SQL 登录名并有足够的权限来执行它以便在其上运行 shell 命令的次数。
回答by Dave
highlight any proc or other system object name in your query editor and hit shift-f1 to get help for that word.
在查询编辑器中突出显示任何 proc 或其他系统对象名称，然后按 shift-f1 以获取该词的帮助。
回答by Dave
Select * From sysobjects where xtype='U' order by Name
Select * From sysobjects where xtype='U' order by Name
Gives a list of all user-defined tables in a database.
给出数据库中所有用户定义表的列表。
