To list Database name,Table name and procedures name in sql server
In this article, I will explain how to get list of database name in the sqlserver,
list of tables name in the cuurent databse
column names in the current table
and
stored procedures names that exist in the current database.
given simple examples
Database Name
To list all database name reside in the SQL Server
Syntax
sp_databases
Example: exec sp_databases
Ouptut:
Database Name Database size Remarks
LearnDB 9664 NULL
master 21440 NULL
msdb 343040 NULL
Northwind 7296 NULL
Tables
to return all the table name for the specified database
Example:
EXEC sp_tables
Output:
Table_Qualifier Table_Owner Table_Name Table_type Remarks
LearnDB dbo sysreferences SYSTEM TABLE NULL
LearnDB dbo systypes SYSTEM TABLE NULL
LearnDB dbo sysusers SYSTEM TABLE NULL
LearnDB dbo Address_Test TABLE NULL
LearnDB dbo Alphabetical list of products TABLE NULL
LearnDB dbo Categories TABLE NULL
Get Column Names
to return column information for the specified tables
Example:
EXEC sp_columns @table_name = 'Address_Test
Output:
Table_Qualifier Table_Owner Table_Name column_name data_type type_name
LearnDB dbo Address_Test Name 12 varchar
LearnDB dbo Address_Test Phone 12 varchar
LearnDB dbo Address_Test Address1 12 varchar
LearnDB dbo Address_Test Address2 12 varchar
LearnDB dbo Address_Test City 12 varchar
LearnDB dbo Address_Test Pincode 4 int
Get column privileges
sp_column_privileges
to Returns column privilege information for a single table
Example:
EXEC sp_column_privileges Address_Test
Output:
Table_Qualifier Table_Owner Table_Name column_name Grantor Grantee Privilege IS_Grantable
LearnDB dbo Address_Test Address1 dbo dbo INSERT YES
LearnDB dbo Address_Test Address1 dbo dbo REFERENCES YES
LearnDB dbo Address_Test Address1 dbo dbo SELECT YES
LearnDB dbo Address_Test Address1 dbo dbo UPDATE YES
LearnDB dbo Address_Test Address2 dbo dbo INSERT YES
LearnDB dbo Address_Test Address2 dbo dbo REFERENCES YES
LearnDB dbo Address_Test Address2 dbo dbo SELECT YES
LearnDB dbo Address_Test Address2 dbo dbo UPDATE YES
LearnDB dbo Address_Test City dbo dbo INSERT YES
LearnDB dbo Address_Test City dbo dbo REFERENCES YES
LearnDB dbo Address_Test City dbo dbo SELECT YES
Get stored procedures
sp_stored_procedures
to Returns a list of stored procedures in the current environment
Example:
EXEC sp_stored_procedures
Output:
Procedure_Qualifier Procedure_Owner Procedure_Name NUM_Input_params NUM_output_params NUM_Result_Sets Remarks Procedure_Type
LearnDB dbo Insert_address;1 -1 -1 -1 NULL 2
LearnDB dbo insert_stud;1 -1 -1 -1 NULL 2
LearnDB dbo ListLocalServers;1 -1 -1 -1 NULL 2
LearnDB dbo istTableRowCounts;1 -1 -1 -1 NULL 2