:::: MENU ::::

Posts Categorized / System Stored Procedures

  • Jul 29 / 2008
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures, T-SQL Scripts

Column names and datatypes in a table

I was required to generate a list of column names along with their data types in a table. For information about a table i used sp_help.

 
USE DBNameHere  
 GO  
 sp_help tableName  
 GO  

And for more to the point result with any filtration required i used following script

Use DBNameHere
GO
SELECT Table_Schema,Table_Name,Column_Name,Data_Type
FROM information_schema.columns
WHERE table_name = ‘TableNameHere’
GO

This produced exact that i wanted.

  • Jul 21 / 2008
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures

Get detailed information about a table with sp_helpDB

To get relevant information about a specific table, following stored procedure may be used

 
USE DBNameHere  
 GO  
 EXEC sp_help tableNameHere  
 GO  

For example in case of adventure works database, if we require information about vVendor table, then our script will be in following form

 
USE AdventureWorks  
 GO  
 EXEC sp_help [Purchasing.vVendor]  
 GO  

Valuable system stored procedure (sp_helpDB) may be used in all versions of SQL Server.

Pages:1234
Consult us to explore the Databases. Contact us