Tables, Views and Stored Procedures via MSSQL information_schema
Recently I posted about using the INFORMATION_SCHEMA table to recover data from a a ISP that had shut me down (see link below). I thought I would just post a summary of how to get tables, views and stored procedures:
To get tables use:
select *
FROM INFORMATION_SCHEMA.TABLES
FROM INFORMATION_SCHEMA.TABLES
To get views use:
select *
FROM INFORMATION_SCHEMA.VIEWS
FROM INFORMATION_SCHEMA.VIEWS
To get stored procs use:
select *
FROM INFORMATION_SCHEMA.ROUTINES
FROM INFORMATION_SCHEMA.ROUTINES
To get columns from a table including defaults etc use:
select *
from information_schema.columns
where table_name = 'your_table_name'
from information_schema.columns
where table_name = 'your_table_name'
To get all the details from a stored procedure you should be able to do something like
SELECT text
FROM syscomments c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.xtype = 'P'
AND o.name = <stored procedure name>
All objects are stored in sysobjects, with the xtype being 'U' for user table, etc. See <a href="http://www.sql-server-performance.com/ak_find_sql_...">How to find a SQL Server Database Object</a> for more information.
Hope that helps