List MS SQL Tables with columns and data types
By Allen Stoner
Here's a quick query to list all the tables in a database joined with their columns and displaying the column types and sizes. Works in MS SQL 2000, 2005 and 2008.
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS 'Schema',
T.[name] AS 'Table name',
AC.[name] AS 'Column name',
TY.[name] AS 'Data type',
AC.[max_length] as 'Size',
AC.[precision] as 'Precision',
AC.[scale] as 'Scale',
AC.[is_nullable] as 'Is Nullable'
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
and T.[name] not in ('sysdiagrams') -- List tables here to not include in the list
ORDER BY
T.[name],
AC.[column_id]
Related FAQs
Microsoft SQL Server 2008 has an XML data for storing such data. A nice thing about using this datatype instead of just a large text column is the ability to query the XML column and retrieve elements and their values. Using this technique you can create a view that makes the XML data look a lot like a regular MS SQL table.
The bold line is the statement to pull the first element with a value of FirstName from within the XML in the xml datatype column.
Often times you want to insert a row into a SQL table that has an identity column and then use the the value of the indentity for the new row in other statements. There are a couple ways to do this in SQl Server.
IDENTITY columns in MS SQL are often used for generating a unique number for each row in a table. As such they do not, by default, allow you to insert your own value, but sometimes it might be necessary.
It rarely happens, but occassionally a MS SQL Server will database will become corrupt. This is often caused by an index getting out of sync and can be repaired with a simple DBCC rebuild. The sample also shows how to put a database into single user mode and take it back out of single user mode.
The easiest way to put XML data into an XML datatype column in Microsoft SQL server is to use the INSERT INTO command and just put the XML in as a string.
List MS SQL Tables with columns and data types (2159 Views)