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
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 <DBNAME>.INFORMATION_SCHEMA.COLUMNS
, without the < and >, gives you the column info for the database DBNAME.
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.
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.
Your other inbuilt friend here is the system sproc SP_HELP.
sample usage ::
sp_help <MyTableName>
It returns a lot more info than you will really need, but at least 90% of your possible requirements will be catered for.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
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
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.
For MYSQL:
Select *
From INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = "<DatabaseName>"
SELECT * FROM INFORMATION_SCHEMA.COLUMNS for get all
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS for get all table name.
Try it on sqlserver,
Just throwing this out there - easy to now copy/paste into a word or google doc:
PRINT '<html><body>'
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 '<h2>' + @tableName + '</h2>'
PRINT '<pre>'
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 '</pre>'
FETCH NEXT FROM tableCursor INTO @tableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
SET NOCOUNT OFF
PRINT '</body></html>'
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'
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.
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'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
Your other inbuilt friend here is the system sproc SP_HELP.
sample usage ::
sp_help <MyTableName>
It returns a lot more info than you will really need, but at least 90% of your possible requirements will be catered for.
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
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'
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'
Your other inbuilt friend here is the system sproc SP_HELP.
sample usage ::
sp_help <MyTableName>
It returns a lot more info than you will really need, but at least 90% of your possible requirements will be catered for.
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'
Just throwing this out there - easy to now copy/paste into a word or google doc:
PRINT '<html><body>'
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 '<h2>' + @tableName + '</h2>'
PRINT '<pre>'
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 '</pre>'
FETCH NEXT FROM tableCursor INTO @tableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
SET NOCOUNT OFF
PRINT '</body></html>'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
For MYSQL:
Select *
From INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = "<DatabaseName>"
SELECT * FROM INFORMATION_SCHEMA.COLUMNS for get all
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS for get all table name.
Try it on sqlserver,
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
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
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.
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
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 <DBNAME>.INFORMATION_SCHEMA.COLUMNS
, without the < and >, gives you the column info for the database DBNAME.
Your other inbuilt friend here is the system sproc SP_HELP.
sample usage ::
sp_help <MyTableName>
It returns a lot more info than you will really need, but at least 90% of your possible requirements will be catered for.
Source: Stackoverflow.com