In today’s article we will see question of one of reader Mohan and answer from expert Imran Mohammed. Imran thank you for answering question of Mohan.
Question of Mohan:
hi all,
how can i get field name and type etc. in MS-SQL server 2005. is there any query available???
Answer from Imran Mohammed:
@mohan
use database_name
Sp_help table_name
This stored procedure gives all the details of column, their types, any indexes, any constraints, any identity columns and some good information for that particular table.
Second method:
select column_name ‘Column Name’, data_type ‘Data Type’, character_maximum_length ‘Maximum Length’ from information_schema.columns where table_name = ‘table_name’
Hope this helps,
Imran.
Let see Imran’s example using sample database AdventureWorks.
USE AdventureWorks
GO
SELECT column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name = 'Address'
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
80 Comments. Leave new
sir .. i have insert , select ,update ,delete query all r write one store procedure .. i will send table name and field value only …
how to ??? please help me…..
You have to use dynamic sql but it will lead to sql injection. Why do you want to do this?
Sir , Insert Store Procedure … I will send Tablename and field value ….
How to do? Please help me ..
@guna
CREATE TABLE A(A VARCHAR(10));
CREATE PROCEDURE INSERT_INTO(@Table_Name VARCHAR(MAX), @Column_Name VARCHAR(MAX), @Value VARCHAR(MAX))
AS
EXECUTE (‘INSERT INTO ‘ + @Table_Name + ‘(‘ + @Column_Name + ‘) VALUES(”’ + @Value + ”’)’);
GO
EXEC INSERT_INTO ‘A’, ‘A’, ‘AAA’;
SELECT * FROM A;
—————-
Note, this is a bad idea for a number of reasons. Please explain why you want to do this so a better idea can be proposed.
Hi All,
I am trying to pull all of the column names from a table in sql 2005 into column ‘A’ of an excel worksheet using vba. I came across this thread and I used the syntax posted here:
strSQL = “Select column_name from information_schema.columns where table_name=’mytblname'”
This works fine, however, when I look at it in my excel worksheet, the first column name is not showing, instead it shows ‘column_name’. The vba code that I am using is below. It pulls the column names in, but then it errors out saying that the item cannot be found in the collection corresponding to the requested name or ordinal. I really would appreciate any helpful suggestions.
strSQL = “Select column_name from information_schema.columns where table_name=’mytblname'”
Set rsmywork = New ADODB.Recordset
rsmywork.Open strSQL, connmywork, adOpenDynamic, adLockReadOnly, -1
For intCount = 1 To rsmywork.Fields.Count
Range(“A2”).Offset(intCount, 1).Value = rsmywork(intCount).OriginalValue
Next
In place of mytablename use the actual table name available
I figured out why it was not showing the first column name. I have fixed my code, but not I am getting another error: item cannot be found in the collection corresponding to the requested name or ordinal.
fixed code –
For intCount = 0 To rsmywork.Fields.Count
Range(“A2”).Offset(intCount, 0).Value = rsmywork.Fields(intCount)
Hello all,
I was querying google to try and find all tables and all their fields with all their data information inside of them and cam accross this web site. Unfortunatly this query was not excatly what I was looking for so here is how to get all information from each field in each table in YOUR database.
select * from [YOUR_DATABASE].information_shcema.columns order by table_name
This is for all who may still be confused just thought I would give out what I found.
Be Well!
Hi All I want to fetch column names and its respective values from table.
How can i do that?
Simple
select col1,col2,…. from table
thank a lot …..
How can this be done if you want to get the column names for a given view?
How can I use this if I am looking for the column names in a view?
Join with information_schema.tables and filter using table_type=’VIEW’
I have a table `validationmaster` with a column called `vrformula`. It contains a formula like:
pf > 1
In that pf is one of the column names in the datasource table. I have to check whether pf of all the entries in the datasource table is > 1 or not, but I don’t know how to make it work.
I can fetch that formula correctly but Sql Server considers that formula as a string, I don’t know how to change that whole expression into a formula.
For example: `select * from datasource where meterid=4716 and pf>=1` is the statement I want to execute, with that formula at the end of the where clause being generated from the `vrformula` column.
Thanks in advance
You need to use dynamic sql
declare @f varchar(100), @sql varchar(8000)
set @f=(select vformula from validationmaster where…)
set @sql=’select * from datasource where meterid=4716 and ‘+@f)
exec(@sql)
how to find out a code type of a column, which has multiple values in it as a drop down.
i had two fields in my sql table.They are id and name.the data are 1,2,3,4,5,6 and x,x,x,y,y,z and the output is 26,24,30.And my question is what is the logic/relation between the data and output?
hi all
This is my first post on this web page and sadly its not a reply but a question
I want to select only those columns from a table having content of columns is not null ,not by mentioning column name manually i want it dynamically.
Hi everyone,
Is there any way to add some details to the column of a table, so that we can store the datatypes used, the meaning of the datatype used, etc …
For example, in a column [User_Type] of table [User], the values stored are as follows :
0 – for user,
1 – for employee,
2 – for employer,
3 – for admin … etc
I just want to store the above information details, which is related to the column specified, in the table/database itself. So that it can be made more programming friendly …
Hi All,
Is it possible to get all the database names, their table names and their feilds names in a single query . if its possible then please share the query so that i can use it in my application.
Thanks
Aijaz
For sql server 2008, for table name dbo.OPERATOR:
select c.name, c.max_length, t.name from sys.columns c join sys.types t on c.system_type_id=t.system_type_id where c.object_id = object_id(‘OPERATOR’);
can u send how to set primary key for the student information system project.
how to retrieve the data from 3 unknown tables by using inner join if there is matching row, but we don’t know which one? means how to find matching or relationship between tables?
@BHARAT,
You/we need to write a complex code for that. Will write blog once I have time.
i wish to fetch values from a table, and i wish to set a threshold/maximum value from which if the column value exceeds then it displays a message. I’m unable to do this so, kindly help me out.
I am not clear with your question. Can you please give example?
Hi Pinal
CREATE TABLE testing
(
testing _id INT NOT NULL
IDENTITY(1,1)
,name varchar (5)
,temp_name SYSNAME
);
SELECT column_name ‘Column Name’,
data_type ‘Data Type’,
CHARacter_maximum_length ‘Maximum Length’
FROM information_schema.columns
WHERE table_name = ‘testing’
I run the above query the output is
output
Column Name Data Type Maximum Length
testing_id int NULL
name varchar 5
temp_name nvarchar 128
In my question
Q1 :
In above table column_name is “temp_name” used datatype is sysname but output is display nvarchar and size is 128 Why?
Q2 :
How to get original datatype name? use only information_schema ?