exec sp_databases; --View Database
exec sp_tables; --View Table
EXEC sp_columns categories;--view columns
EXEC sp_helpindex categories;--View index
EXEC sp_helpconstraint categories;--constraints
exec sp_stored_procedures;
exec sp_helptext ' sp_stored_procedures ';--view stored procedure creation, definition statement
EXEC sp_rename Student, stuinfo;--modify the name of the table, index, column
exec sp_renamedb mytempdb, mydb;--change database name
exec sp_defaultdb ' master ', ' MyDB ';--Change the default database for logins
EXEC sp_helpdb;--database help, querying database information
exec sp_helpdb master;
--Query the number of rows in the database, the allocated capacity, the capacity used for the data, and the index usage capacity
IF object_id (' tempdb.. #temp ') is not NULL
BEGIN
DROP TABLE #temp
END
CREATE TABLE #temp
(
name varchar,
rows INT,
reserved varchar),
Data VARCHAR,
Inde X_size varchar,
unused varchar ()
)
DECLARE @tablename varchar (+)
DECLARE @sql varchar (
DECLARE @str VARCHAR
DECLARE tablecursor CURSOR
for
SELECT name
from sysobjects
WHERE Xtype = ' u '
OPEN tablecursor
FETCH NEXT from Tablecursor to @tablename
while @ @fetch_status = 0
BEGIN
Set @str = @tablename
SET @sql = ' INSERT into #temp exec sp_spaceused ' + @tablename
Exec (@sql)
FETCH NEXT F ROM tablecursor to @tablename
END
CLOSE tablecursor
deallocate tablecursor
SELECT *
from #temp