SQL and Database: Table Information Query

useful info
1

Thanks to @Nabiki for splitting out the SQL goodness into separate threads to make solutions easier to find!

Came across a little query I forgot I had that could be useful to some here. It gives you a whole mess of information about a table in your database, including the name of all the fields, what type each field is, the max length, etc.

SELECT c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
    
FROM sys.columns AS c 
	JOIN sys.types AS t ON c.user_type_id=t.user_type_id

--CHANGE TABLE NAME IN WHERE CLAUSE
WHERE c.object_id = OBJECT_ID('[Your Table Here]')

ORDER BY c.column_id;
2

@TechnoMistress, for better readability, enclose your SQL code in code blocks. You can either highlight it in the edit window and hit the code button in the edit window toolbar (it looks like a slash enclosed in pointy brackets - </>) or put three backticks above and below your code—

```
code goes here
```
3

sp_help TABLENAME is nice too. Or put the cursor on the table name and hit Alt+F1. Which I think works for other objects as well.

4

Thanks, Lee - I wanted to do that, but didn’t know how. When I tried the </> button it didn’t quite work right, but I think that was because I was doing that first and then cutting and pasting the code rather than the other way around.

5

One advantage to the query is that it gives precision and scale even for defaults where sp_help expects me to remember that stuff. Nice.

6

I don’t even remember where I got the query - Google, no doubt. :smile: I certainly didn’t write it, I’m not that good (yet…)