Speed up Development on Backend and Middle Tier with SQL Scripts
page 3 of 6
by Ameet Phadnis
Average Rating: 
Views (Total / Last 10 Days): 28616/ 78

Accessing Meta Data - System Stored Procedures

Another way of accessing data about data is system stored procedures. I will explain the system stored procedures for the above context except for foreign keys as it is a bit complex. Also, while researching the foreign keys system stored procedure I came across the Schema views.


Sp_Tables: This stored procedure returns back all the table names. For example, to get all user defined tables from XYZ database, your SQL will look like –


EXEC sp_tables NULL, dbo, XYZ, "'TABLE'"


Sp_columns: This stored procedure returns back all the columns for specified table. Let’s consider the example above. The best part about this stored procedure is it hides the complexity of joins to get the data types and column names as shown in system tables section. To get all columns in the Users table you will call this procedure as –


Exec sp_columns 'Users'


sp_datatype_info: This stored procedure returns back information on all data types.


Syntax is going to be-


EXEC sp_datatype_info

View Entire Article

User Comments

Title: MetaData   
Name: Sanjay Modi
Date: 2007-04-10 2:25:25 PM
its realy helpful my best wishes to user

Sanjay Modi
Title: ing   
Name: Jorgen Pedersen
Date: 2005-09-05 12:10:14 PM
Hi. I like your article but I need to find the name of the columns constraint name for the DEFAULT value of the column. Can you help?
Yjanks in advance
Title: Thank you   
Name: Mab
Date: 2005-06-21 8:37:32 AM
Its really a very gud articles for the developers. Thanks for sharing such a nice article.


Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-06-15 3:47:51 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search