How can I print table structures in SQLExpress

I need to get a print out of column names, types, and properties for each of the tables. We just started using SQL Express from Access. I cannot find any reference to printing the structures in Microsoft SQL Server Management Studio Express.

Thanks

hi,

this is actually non avaliable at all..

you can have a "similar" result via SQL Server Management Studio Express (http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en) "Diagrams" features.. add all required tables + views and print it out.. not very beautifull but available.. or get them to clipboard and pasting them into Paint or the like...

regards

|||

Sigh............................

Thanks

|||

Rich

Perhaps this will help. Query your database with this:

SELECT * FROM
INFORMATION_SCHEMA.COLUMN

Copy the results into excel or something and get rid of the column you don't need.

|||

Subtle wrote:

Rich

Perhaps this will help. Query your database with this:

SELECT * FROM
INFORMATION_SCHEMA.COLUMN

Copy the results into excel or something and get rid of the column you don't need.

How about using sp_describe_cursor_columns? Then you can print the output from C# or something?

|||

hi,

I'd better go for INFORMATION_SCHEMA ANSI view or, if your really want to go a quiet unsupported way, you could even resort on an unsupported/undocumented stored procedure, sp_MSForEach (http://www.databasejournal.com/features/mssql/article.php/3441031)..

then you can define your query to be executed against all database tables...

for instance something based on catalog views, similar to

SET NOCOUNT ON;

USE AdventureWorks;

GO

DECLARE @.cmd varchar(MAX);

SET @.cmd = 'SELECT ''?'' AS [ObjName];

SELECT clmns.name AS [Name]

, usrt.name AS [DataType], ISNULL(baset.name, N'''') AS [SystemType]

, clmns.is_nullable, clmns.is_identity, clmns.is_rowguidcol, clmns.is_computed

, CASE WHEN clmns.name IN (''timestamp'', ''rowversion'') THEN 1 ELSE 0 END AS is_timestamp

, CASE WHEN clmns.default_object_id <> 0 THEN 1 ELSE 0 END AS [has_default_value]

, CASE WHEN clmns.default_object_id = 0 THEN '''' ELSE

(SELECT d.definition

FROM sys.default_constraints d

WHERE(clmns.object_id = d.parent_object_id)

AND d.parent_column_id = clmns.column_id)

END AS [Default Definition]

, CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length]

, CAST(clmns.precision AS int) AS [NumericPrecision]

, CAST(clmns.scale AS int) AS [NumericScale]

FROM sys.tables AS tbl

INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id

LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id

WHERE clmns.object_id = OBJECT_ID(''?'')

ORDER BY clmns.column_id ASC;'

EXEC sp_MSforeachtable @.cmd;

--<--

ObjName

-

[Production].[ProductProductPhoto]

Name DataType SystemType is_nullable is_identity is_rowguidcol is_computed is_timestamp has_default_value Default Definition Length NumericPrecision NumericScale

- -- -- -- - -- -- -- -- -

ProductID int int 0 0 0 0 0 0 4 10 0

ProductPhotoID int int 0 0 0 0 0 0 4 10 0

Primary Flag bit 0 0 0 0 0 1 ((0)) 1 1 0

ModifiedDate datetime datetime 0 0 0 0 0 1 (getdate()) 8 23 3

...other objs..

you are then obviously free to add/remove projections as required..

regards

how can I open a .mdf aspnetDB DataBase ?

Hello everybody,

I am working with SqlExpress and MSSQL Server Management Studio Express and do not manage to open the aspnetDB .mdf database in which I am storing all data about my users.

I woulds like to open it and to be able to request it in order to check some strange things.

Actually, I'm experiencing an isssue with that database since the request just below gives me two results for only one user:

SELECT Member.Password, Member.PasswordAnswerFROM aspnet_MemberShip Member, aspnet_Users UsersWHERE Member.UserId=Users.UserIdAND Users.UserName='Admin'

Using this request returns me a user whom password is clear and an identical user whom password is hashed whereas I set PasswordFormat="Clear" in my Web.Config

Could you explain me how I may proceed please?

Thanx for your help

If you still have your project open, it could be that the project has a lock on the database file. Close your project in order to use MS SSMS express.

Otherwise, you can right-click the ASPNET.MDF database file and choose 'Open'. This will allow you to navigate the database using the Server Explorer tab, much like you would with SSMS.

Your problem lies in your SQL, however. UserId is the only Unique column in the default Users table because it is the primary key. This means that the UserName is NOT unique, and could be duplicated.

Use System.Web.Security.Membership.Provider to get a reference to the app's default membership provider, and use that to do what you need. (See the System.Web.Security.MembershipProvider class for available functions.)

|||

Thanx for your answer ps2goat,

I finally used VisualStudio Servers's Explorer to do so and was able to see all the information stored in the .mdf file.

This raises some questions about the issue I was dealing about above, but at the time, it is a solution for that precise problem ;)

In fact, I had two identical usernames in the database, one with a password saved in clear and the other hashed and, as you tell it, with a different unique ID.

I believe this comes from the fact that I used this .mdf file in more than 3 versions of the current prooject, making of it some kind of mess where it becomes to difficult to find what you're looking for.

Thanks for your help.

|||

You can just use visual studio directly to navigate through it. Also you can get that into sql express or any other db and reconstruct all the db.