Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Actually, you will discover that your can do this yourself and you will learn something in the process. I use this on several databases I maintain. I create a view that makes it easy to look use DDL style info.</p> <pre><code>create view vw_help as select Table_Name as TableName , Column_Name as ColName , Ordinal_Position as ColNum , Data_Type as DataType , Character_Maximum_Length as MaxChars , coalesce(Datetime_Precision, Numeric_Precision) as [Precision] , Numeric_Scale as Scale , Is_Nullable as Nullable , case when (Data_Type in ('varchar', 'nvarchar', 'char', 'nchar', 'binary', 'varbinary')) then case when (Character_Maximum_Length = -1) then Data_Type + '(max)' else Data_Type + '(' + convert(varchar(6),Character_Maximum_Length) + ')' end when (Data_Type in ('decimal', 'numeric')) then Data_Type + '(' + convert(varchar(4), Numeric_Precision) + ',' + convert(varchar(4), Numeric_Scale) + ')' when (Data_Type in ('bit', 'money', 'smallmoney', 'int', 'smallint', 'tinyint', 'bigint', 'date', 'time', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset', 'datetime2', 'float', 'real', 'text', 'ntext', 'image', 'timestamp', 'uniqueidentifier', 'xml')) then Data_Type else 'unknown type' end as DeclA , case when (Is_Nullable = 'YES') then 'null' else 'not null' end as DeclB , Collation_Name as Coll -- ,* from Information_Schema.Columns GO </code></pre> <p>And I use the following to "show the table structure"</p> <pre><code>/* exec ad_Help TableName, 1 */ ALTER proc [dbo].[ad_Help] (@TableName nvarchar(128), @ByOrdinal int = 0) as begin set nocount on declare @result table ( TableName nvarchar(128) , ColName nvarchar(128) , ColNum int , DataType nvarchar(128) , MaxChars int , [Precision] int , Scale int , Nullable varchar(3) , DeclA varchar(max) , DeclB varchar(max) , Coll varchar(128) ) insert @result select TableName, ColName, ColNum, DataType, MaxChars, [Precision], Scale, Nullable, DeclA, DeclB, Coll from dbo.vw_help where TableName like @TableName if (select count(*) from @result) &lt;= 0 begin select 'No tables matching ''' + @TableName + '''' as Error return end if (@ByOrdinal &gt; 0) begin select * from @result order by TableName, ColNum end else begin select * from @result order by TableName, ColName end end GO </code></pre> <p>You can use other info in InformationSchemas if you also need to generate Foreign keys, etc. It is a bit complex and I never bothered to flesh out everything necessary to generate the DDL, but you should get the right idea. Of course, I would not bother with rolling your own if you can use what has already been suggested.</p> <p>Added comment -- I did not give you an exact answer, but glad to help. You will need to generate lots of dynamic string manipulation to make this work -- varchar(max) helps. I will point out the TSQL is not the language of choice for this kind of project. Personally, if I had to generate full table DDL's I might be tempted to write this as a CLR proc and do the heavy string manipulation in C#. If this makes sense to you, I would still debug the process outside of SQL server (e.g. a form project for testing and dinking around). Just remember that CLR procs are Net 2.0 framework. </p> <p>You can absolutely make a stored proc that returns a set of results, i.e., 1 for the table columns, 1 for the foreign keys, etc. then consume that set of results in C# and built the DDL statements. in C# code.</p>
    singulars
    1. This table or related slice is empty.
    1. POSSIS: generate create table DDL programatically
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGary Walker
    1. USGary Walker
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSSIS: generate create table DDL programatically
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks, this is what I was looking for.
      singulars
      1. PO
      1. USMetaphor
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload