Never been to CodeSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

Get column names from MSSQL (See related posts)

SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U'
ORDER BY sysobjects.name,syscolumns.colid
to sql ms-sql by gregf on Mon Feb 06 03:31:10 +0000 2006

Comments on this post

karlkatzke posts on Apr 19, 2006 at 15:46
The posted query gets ALL table names for a database.

There's an easier way to do this, and you can do it for a specific table.

SELECT * FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'Foo' ... where Foo is the table name you want details for.
atuldpatil posts on Oct 08, 2007 at 11:54

Above is really useful query. I was looking for the one since long time. They have explained hell lot of useless queries in msdn (least useful resource for developers!) but not simple ones. Giving here useful and simple SQL statements.

1. Get total rows of table:
SELECT count(*) FROM <table_name>

2. Get total rows having field value null:
SELECT count(*) FROM <table_name> where <field_name> is null

3. Get column names of table:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'ipspingdata'

4. Get all rows of table:
SELECT * from <table_name>

5. Delete all rows of table (USE WITH CAUTION):
delete from <table_name>

6. Delete table (USE WITH CAUTION):
drop table <table_name>

7. Get reasons of job failure of SQL Agent jobs:
select * from MSDB.dbo.sysjobhistory
atuldpatil posts on Oct 08, 2007 at 12:00

OOOPS.. third point should be:

3. Get column names of table:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = <table_name>

(Sorry I had posted name of table am working on :-)
webmonkeymon posts on Apr 10, 2008 at 01:53
no doubt this script rocks!
Just what i needed. Well I was looking to search a single column name across all tables.
which is just a slight modification.

to search all tables to find a column or field name in a database
===================================

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE syscolumns.name='YourColumnName"



You need to create an account or log in to post comments to this site.


Related Posts