How to get table definitions from a table in SQL Server via SQL.?

Question

Does anyone know the SQL code to generate the table definitions for a particular table in SQL Server?

If it helps, I know the syntax for mySQL is below. I know how to do it through Enterprise Manager's interface, but I want to know how to do it entirely from Query Analyzer via SQL Code. Thanks,

SHOW CREATE TABLE ;

Answer 1

sp_help MyTable



UPDATE: That's the quick/dirty way. If you want a script, here's one I found that might work for you. The script isn't as robust as the one that EM will generate for you, but I suspect it works.



declare @vsSQL varchar(8000)

declare @vsTableName varchar(50)

select @vsTableName = 'Customers'



select @vsSQL = 'CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10)



select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' +

st.Name +

case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) + ') ' else ' ' end +

case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10)

from sysobjects so

join syscolumns sc on sc.id = so.id

join systypes st on st.xusertype = sc.xusertype

where so.name = @vsTableName

order by

sc.ColID



select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ')'



Note that when I tested this, I had to change the output parameter of the SQL results pane to support the number of characters, as the entire script is concatenated into one long string at runtime. If I were to write my own from scratch, I'd use nested cursors to fetch from syscolumns while inside a sysobjects loop.

Answer 2

My wager is even as they manually added your consumer id after the improve, they did not supply you an same set of permissions, or placed you interior of an same roles that you had previous to the improve. So i'm certain it really is a server situation. The table is interior the database, because your colleagues can see it. you purely haven't any permissions on that table. You DBA should be able to make sure out what were given neglected quite actual.

Answer 3

if you want the actual code to generate a table,

you can open query analyzer,

show the object browser (F8) if it's not already there.

navigate to the object you want the code for,

right click on it.

select "script to new window as->create"

this will open a new window with the sql code

to create the object.