Getting list of tables and fields in each in a database

Question

I m looking at creating a basic ORM  purely for fun   and was wondering  is there a way to return the list of tables in a database and also the fields for every table   Using this  I want to be able to loop through the result set  in C   and then say for each table in the result set  do this  e g  use reflection to make a class that will do or contain xyz    Further to this  what are some good online blogs for SQL Server  I know this question is really about using system SPs and databases in Sql Server  and I am ok with general queries  so I m interested in some blogs which cover this sort of functionality   Thanks

User · Answer

This will get you all the user created tables:

select * from sysobjects where xtype='U'

To get the cols:

Select * from Information_Schema.Columns Where Table_Name = 'Insert Table Name Here'

Also, I find http://www.sqlservercentral.com/ to be a pretty good db resource.

User · Answer

Is this what you are looking for   Using OBJECT CATALOG VIEWS   SELECT T name AS Table Name          C name AS Column Name          P name AS Data Type          P max length AS Size          CAST P precision AS VARCHAR          CAST P scale AS VARCHAR  AS Precision Scale FROM   sys objects AS T        JOIN sys columns AS C ON T object id   C object id        JOIN sys types AS P ON C system type id   P system type id WHERE  T type desc    USER TABLE     Using INFORMATION SCHEMA VIEWS    SELECT TABLE SCHEMA          TABLE NAME          COLUMN NAME          ORDINAL POSITION          COLUMN DEFAULT          DATA TYPE          CHARACTER MAXIMUM LENGTH          NUMERIC PRECISION          NUMERIC PRECISION RADIX          NUMERIC SCALE          DATETIME PRECISION FROM   INFORMATION SCHEMA COLUMNS    Reference   My Blog - http   dbalink wordpress com 2008 10 24 querying-the-object-catalog-and-information-schema-views

User · Answer

This will return the database name  table name  column name and the datatype of the column specified by a database parameter   declare  database nvarchar 25  set  database       SELECT cu table catalog cu VIEW SCHEMA  cu VIEW NAME  cu TABLE NAME     cu COLUMN NAME c DATA TYPE c character maximum length from INFORMATION SCHEMA VIEW COLUMN USAGE as cu JOIN INFORMATION SCHEMA COLUMNS as c on cu TABLE SCHEMA   c TABLE SCHEMA and c TABLE CATALOG    cu TABLE CATALOG and c TABLE NAME   cu TABLE NAME and c COLUMN NAME   cu COLUMN NAME where cu TABLE CATALOG    database order by cu view name c COLUMN NAME

User · Answer

Tables     SELECT   FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE  BASE TABLE    columns     SELECT   FROM INFORMATION SCHEMA COLUMNS    or   SELECT   FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME  your table name

User · Answer

Tables     SELECT   FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE  BASE TABLE    columns     SELECT   FROM INFORMATION SCHEMA COLUMNS    or   SELECT   FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME  your table name

User · Answer

Is this what you are looking for   Using OBJECT CATALOG VIEWS   SELECT T name AS Table Name          C name AS Column Name          P name AS Data Type          P max length AS Size          CAST P precision AS VARCHAR          CAST P scale AS VARCHAR  AS Precision Scale FROM   sys objects AS T        JOIN sys columns AS C ON T object id   C object id        JOIN sys types AS P ON C system type id   P system type id WHERE  T type desc    USER TABLE     Using INFORMATION SCHEMA VIEWS    SELECT TABLE SCHEMA          TABLE NAME          COLUMN NAME          ORDINAL POSITION          COLUMN DEFAULT          DATA TYPE          CHARACTER MAXIMUM LENGTH          NUMERIC PRECISION          NUMERIC PRECISION RADIX          NUMERIC SCALE          DATETIME PRECISION FROM   INFORMATION SCHEMA COLUMNS    Reference   My Blog - http   dbalink wordpress com 2008 10 24 querying-the-object-catalog-and-information-schema-views

User · Answer

Your other inbuilt friend here is the system sproc SP HELP   sample usage      sp help  lt MyTableName gt    It returns a lot more info than you will really need  but at least 90  of your possible requirements will be catered for

User · Answer

I tested a few solutions an found that   Select   From INFORMATION SCHEMA COLUMNS   gives you the column info for your CURRENT default database   Select   From  lt DBNAME gt  INFORMATION SCHEMA COLUMNS     without the  lt  and    gives you the column info for the database DBNAME

User · Answer

Your other inbuilt friend here is the system sproc SP HELP   sample usage      sp help  lt MyTableName gt    It returns a lot more info than you will really need  but at least 90  of your possible requirements will be catered for

User · Answer

in a Microsoft SQL Server you can use this   declare  sql2 nvarchar 2000          set  sql2     use   if    db name db id    not in    master     tempdb     model     msdb     SSISDB       begin     select     db name   as db      SS name as schemaname      SO name tablename      SC name columnname      ST name type      case when ST name in    nvarchar      nchar            then convert varchar 10     SC max length   2            when ST name in    char      varchar            then convert varchar 10   SC max length          else null     end as length      case when SC is nullable   0 then   No   when SC is nullable   1 then   Yes   else null end as nullable      isnull SC column id 0  as col number from sys objects                  SO join sys schemas                  SS     on SS schema id   SO schema id join sys columns             SC on SO object id       SC object id left join sys types               ST     on SC user type id   ST user type id and SC system type id   ST system type id     where SO is ms shipped   0  end    exec sp msforeachdb  command1    sql2   this shows you all tables and columns   and their definition   from all userdefined databases

User · Answer

Is this what you are looking for   Using OBJECT CATALOG VIEWS   SELECT T name AS Table Name          C name AS Column Name          P name AS Data Type          P max length AS Size          CAST P precision AS VARCHAR          CAST P scale AS VARCHAR  AS Precision Scale FROM   sys objects AS T        JOIN sys columns AS C ON T object id   C object id        JOIN sys types AS P ON C system type id   P system type id WHERE  T type desc    USER TABLE     Using INFORMATION SCHEMA VIEWS    SELECT TABLE SCHEMA          TABLE NAME          COLUMN NAME          ORDINAL POSITION          COLUMN DEFAULT          DATA TYPE          CHARACTER MAXIMUM LENGTH          NUMERIC PRECISION          NUMERIC PRECISION RADIX          NUMERIC SCALE          DATETIME PRECISION FROM   INFORMATION SCHEMA COLUMNS    Reference   My Blog - http   dbalink wordpress com 2008 10 24 querying-the-object-catalog-and-information-schema-views

User · Answer

Get list of all the tables and the fields in database   Select   From INFORMATION SCHEMA COLUMNS Where TABLE CATALOG Like  DatabaseName    Get list of all the fields in table   Select   From INFORMATION SCHEMA COLUMNS Where TABLE CATALOG Like  DatabaseName  And TABLE NAME Like  TableName

User · Answer

For MYSQL   Select   From INFORMATION SCHEMA COLUMNS where TABLE SCHEMA     lt DatabaseName gt

User · Answer

in a Microsoft SQL Server you can use this   declare  sql2 nvarchar 2000          set  sql2     use   if    db name db id    not in    master     tempdb     model     msdb     SSISDB       begin     select     db name   as db      SS name as schemaname      SO name tablename      SC name columnname      ST name type      case when ST name in    nvarchar      nchar            then convert varchar 10     SC max length   2            when ST name in    char      varchar            then convert varchar 10   SC max length          else null     end as length      case when SC is nullable   0 then   No   when SC is nullable   1 then   Yes   else null end as nullable      isnull SC column id 0  as col number from sys objects                  SO join sys schemas                  SS     on SS schema id   SO schema id join sys columns             SC on SO object id       SC object id left join sys types               ST     on SC user type id   ST user type id and SC system type id   ST system type id     where SO is ms shipped   0  end    exec sp msforeachdb  command1    sql2   this shows you all tables and columns   and their definition   from all userdefined databases

User · Answer

This will get you all the user created tables:

select * from sysobjects where xtype='U'

To get the cols:

Select * from Information_Schema.Columns Where Table_Name = 'Insert Table Name Here'

Also, I find http://www.sqlservercentral.com/ to be a pretty good db resource.

User · Answer

SELECT   FROM INFORMATION SCHEMA COLUMNS for get all    SELECT TABLE NAME FROM INFORMATION SCHEMA COLUMNS for get all table name  Try it on sqlserver

User · Answer

I found an easy way to fetch the  details of Tables and columns of a particular DB using SQL developer   Select  FROM USER TAB COLUMNS

User · Answer

SELECT   FROM INFORMATION SCHEMA COLUMNS

User · Answer

Just throwing this out there - easy to now copy paste into a word or google doc   PRINT   lt html gt  lt body gt   SET NOCOUNT ON DECLARE  tableName VARCHAR 30  DECLARE tableCursor CURSOR LOCAL FAST FORWARD FOR     SELECT T name AS TableName        FROM sys objects AS T      WHERE T type desc    USER TABLE       ORDER BY T name OPEN tableCursor FETCH NEXT FROM tableCursor INTO  tableName WHILE   FETCH STATUS   0 BEGIN     PRINT   lt h2 gt      tableName     lt  h2 gt       PRINT   lt pre gt       SELECT LEFT C name  30  AS ColumnName             LEFT ISC DATA TYPE  10  AS DataType             C max length AS Size             CAST P precision AS VARCHAR 4           CAST P scale AS VARCHAR 4   AS PrecScale             CASE WHEN C is nullable   1 THEN  Null  ELSE  No Null  END AS  Nullable              LEFT ISNULL ISC COLUMN DEFAULT        5   AS  Default              CASE WHEN C is identity   1 THEN  Identity  ELSE    END AS  Identity      FROM   sys objects AS T            JOIN sys columns AS C ON T object id   C object id            JOIN sys types AS P ON C system type id   P system type id            JOIN INFORMATION SCHEMA COLUMNS AS ISC ON T name   ISC TABLE NAME AND C name   ISC COLUMN NAME     WHERE  T type desc    USER TABLE        AND  T name    tableName     ORDER BY T name  ISC ORDINAL POSITION     PRINT   lt  pre gt       FETCH NEXT FROM tableCursor INTO  tableName  END  CLOSE tableCursor DEALLOCATE tableCursor SET NOCOUNT OFF PRINT   lt  body gt  lt  html gt

User · Answer

For MYSQL   Select   From INFORMATION SCHEMA COLUMNS where TABLE SCHEMA     lt DatabaseName gt

User · Answer

Is this what you are looking for   Using OBJECT CATALOG VIEWS   SELECT T name AS Table Name          C name AS Column Name          P name AS Data Type          P max length AS Size          CAST P precision AS VARCHAR          CAST P scale AS VARCHAR  AS Precision Scale FROM   sys objects AS T        JOIN sys columns AS C ON T object id   C object id        JOIN sys types AS P ON C system type id   P system type id WHERE  T type desc    USER TABLE     Using INFORMATION SCHEMA VIEWS    SELECT TABLE SCHEMA          TABLE NAME          COLUMN NAME          ORDINAL POSITION          COLUMN DEFAULT          DATA TYPE          CHARACTER MAXIMUM LENGTH          NUMERIC PRECISION          NUMERIC PRECISION RADIX          NUMERIC SCALE          DATETIME PRECISION FROM   INFORMATION SCHEMA COLUMNS    Reference   My Blog - http   dbalink wordpress com 2008 10 24 querying-the-object-catalog-and-information-schema-views

User · Answer

I tested a few solutions an found that   Select   From INFORMATION SCHEMA COLUMNS   gives you the column info for your CURRENT default database   Select   From  lt DBNAME gt  INFORMATION SCHEMA COLUMNS     without the  lt  and    gives you the column info for the database DBNAME

User · Answer

SELECT   FROM INFORMATION SCHEMA COLUMNS

User · Answer

SELECT   FROM INFORMATION SCHEMA COLUMNS for get all    SELECT TABLE NAME FROM INFORMATION SCHEMA COLUMNS for get all table name  Try it on sqlserver

User · Answer

This will get you all the user created tables:

select * from sysobjects where xtype='U'

To get the cols:

Select * from Information_Schema.Columns Where Table_Name = 'Insert Table Name Here'

Also, I find http://www.sqlservercentral.com/ to be a pretty good db resource.

User · Answer

This will get you all the user created tables:

select * from sysobjects where xtype='U'

To get the cols:

Select * from Information_Schema.Columns Where Table_Name = 'Insert Table Name Here'

Also, I find http://www.sqlservercentral.com/ to be a pretty good db resource.

User · Answer

This will return the database name  table name  column name and the datatype of the column specified by a database parameter   declare  database nvarchar 25  set  database       SELECT cu table catalog cu VIEW SCHEMA  cu VIEW NAME  cu TABLE NAME     cu COLUMN NAME c DATA TYPE c character maximum length from INFORMATION SCHEMA VIEW COLUMN USAGE as cu JOIN INFORMATION SCHEMA COLUMNS as c on cu TABLE SCHEMA   c TABLE SCHEMA and c TABLE CATALOG    cu TABLE CATALOG and c TABLE NAME   cu TABLE NAME and c COLUMN NAME   cu COLUMN NAME where cu TABLE CATALOG    database order by cu view name c COLUMN NAME

User · Answer

SELECT   FROM INFORMATION SCHEMA COLUMNS

User · Answer

Tables     SELECT   FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE  BASE TABLE    columns     SELECT   FROM INFORMATION SCHEMA COLUMNS    or   SELECT   FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME  your table name

User · Answer

Your other inbuilt friend here is the system sproc SP HELP   sample usage      sp help  lt MyTableName gt    It returns a lot more info than you will really need  but at least 90  of your possible requirements will be catered for

User · Answer

SELECT   FROM INFORMATION SCHEMA COLUMNS

User · Answer

Just throwing this out there - easy to now copy paste into a word or google doc   PRINT   lt html gt  lt body gt   SET NOCOUNT ON DECLARE  tableName VARCHAR 30  DECLARE tableCursor CURSOR LOCAL FAST FORWARD FOR     SELECT T name AS TableName        FROM sys objects AS T      WHERE T type desc    USER TABLE       ORDER BY T name OPEN tableCursor FETCH NEXT FROM tableCursor INTO  tableName WHILE   FETCH STATUS   0 BEGIN     PRINT   lt h2 gt      tableName     lt  h2 gt       PRINT   lt pre gt       SELECT LEFT C name  30  AS ColumnName             LEFT ISC DATA TYPE  10  AS DataType             C max length AS Size             CAST P precision AS VARCHAR 4           CAST P scale AS VARCHAR 4   AS PrecScale             CASE WHEN C is nullable   1 THEN  Null  ELSE  No Null  END AS  Nullable              LEFT ISNULL ISC COLUMN DEFAULT        5   AS  Default              CASE WHEN C is identity   1 THEN  Identity  ELSE    END AS  Identity      FROM   sys objects AS T            JOIN sys columns AS C ON T object id   C object id            JOIN sys types AS P ON C system type id   P system type id            JOIN INFORMATION SCHEMA COLUMNS AS ISC ON T name   ISC TABLE NAME AND C name   ISC COLUMN NAME     WHERE  T type desc    USER TABLE        AND  T name    tableName     ORDER BY T name  ISC ORDINAL POSITION     PRINT   lt  pre gt       FETCH NEXT FROM tableCursor INTO  tableName  END  CLOSE tableCursor DEALLOCATE tableCursor SET NOCOUNT OFF PRINT   lt  body gt  lt  html gt

User · Answer

Your other inbuilt friend here is the system sproc SP HELP   sample usage      sp help  lt MyTableName gt    It returns a lot more info than you will really need  but at least 90  of your possible requirements will be catered for

User · Answer

I found an easy way to fetch the  details of Tables and columns of a particular DB using SQL developer   Select  FROM USER TAB COLUMNS

User · Answer

Tables     SELECT   FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE  BASE TABLE    columns     SELECT   FROM INFORMATION SCHEMA COLUMNS    or   SELECT   FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME  your table name

User · Answer

Get list of all the tables and the fields in database   Select   From INFORMATION SCHEMA COLUMNS Where TABLE CATALOG Like  DatabaseName    Get list of all the fields in table   Select   From INFORMATION SCHEMA COLUMNS Where TABLE CATALOG Like  DatabaseName  And TABLE NAME Like  TableName

[sql] Getting list of tables, and fields in each, in a database

Examples related to sql

Examples related to tsql