Mssql returns the table creation statement. mssql returns the creation statement.

Mssql returns the table creation statement. mssql returns the creation statement.

If OBJECT_ID ('SP _ create_table_ SQL ', 'P') is not nullddrop proc sp_create_table_sqlgocreate proc sp_create_table_ SQL (@ tablename varchar (255 )) as begin -- exec sp_create_table_ SQL 'ad _ adgroup' -- 0. hong En-1. indexes of non-primary key classes are not supported -- 2. non-default sorting of the primary/secondary keys is not supported -- 3. DEFAULT -- 4. calculation column is not supported -- 5. to be complete, declare @ SQL _create varchar (max) = ''; declare @ SQL _column varchar (max); declare @ SQL _primary varchar (max); with cte as (select QUOTENAME (c. name) + ''+ TYPE_NAME (c. system_type_id) + ''+ case when TYPE_NAME (c. system_type_id) in ('Char ', 'varchar', 'decimal') then' ('else' 'end + case when TYPE_NAME (c. system_type_id) in ('Char ', 'varchar', 'nvarchar ') then cast (max_length as varchar) else ''end + case when TYPE_NAME (c. system_type_id) in ('decimal') then cast (c. precision as varchar) + ',' + cast (c. scale AS varchar) else ''end + case when TYPE_NAME (c. system_type_id) in ('Char ', 'varchar', 'decimal') then') 'else' end + case when c. is_nullable = 1 then 'null' else 'not null' end + case when c. is_identity = 0 then ''else' identity 'end sqlstr, column_id from sys. objects as o join sys. columns as c on o. object_id = c. object_id where o. name = @ tablename and o. type = 'U') select @ SQL _column = stuff (select ',' + sqlstr + CHAR (10) from cte order by column_id asc for xml path ('')), 1, 1, ''); select @ SQL _primary = stuff (select ',' + c. name from sys. index_columns as I join sys. indexes as ix on I. object_id = ix. object_id and I. index_id = ix. index_id join sys. columns as c on I. object_id = c. object_id and I. column_id = c. column_id where OBJECT_NAME (I. object_id) = @ tablename and ix. is_primary_key = 1 order by I. key_ordinal for xml path (''), 1, 1 ,'') set @ SQL _create = 'create table' + @ tablename + '(' + @ SQL _column + case when len (@ SQL _primary)> = 1 then (', primary key ('+ @ SQL _primary +') else ''end + ') 'print' -- @ SQL _create -- 'print @ SQL _createend

Mssql: used to export table creation statements. Note: Use code to export the statements.

First, the function of this stored procedure is:
Enter a table name, execute the stored procedure, and return an insert statement for each record in the table.
(For example, if Table A (a, B, c) has three records (, 3;, 6;, 9), the returned result is:
Insert into a values ('1', '2', '3 ')
Insert into a values ('4', '5', '6 ')
Insert into a values ('7', '8', '9 ')
-----------------------------------------------------------
Explain the process as follows:
ALTER proc -- modify stored procedure
DECLARE xCursor cursor for -- defines the CURSOR
OPEN xCursor
FETCH xCursor into @ F1, @ F2 -- open the cursor, assign a value to the variable, and start the loop.

WHILE @ FETCH_STATUS = 0 -- use the global variable @ FETCH_STATUS to determine whether the current cursor is valid (= 0 indicates that the cursor value is successful)
CLOSE xCursor -- CLOSE the cursor
Exec (@ SQL) -- execute dynamic SQL statements. @ SQL is an SQL statement assembled by a cursor.

Hope to help you


The MSSQL database stores data in a table with fields. How can this problem be solved?

Right-click a table name