March 19, 2014 at 10:27 am
Hi I am trying to build something that needs to be very dynamic that mimic a replication, for that I will need to get the scripts of some of my tables, index included as to create the tables on another repository and apply changes, is there a way to get those scripts without the wizard in an easy way?
I can build the table getting info from sp_help and the indexes from other sources but it is a bit complex and I was looking for something more simple
Thanks!
March 19, 2014 at 10:34 am
See if this helps:
SELECT
DB_NAME() AS DatabaseName, T.[name] AS Table_Name, AC.[name] AS Column_Name,
AC.column_id AS Ordinal_Position, TY.[name] AS Data_type,
CASE WHEN AC.[precision] = 0 THEN AC.max_length ELSE AC.[precision] END AS CLength,
AC.is_nullable AS IsNullable,
OBJECT_DEFINITION(AC.default_object_id) AS Column_Default
FROM sys.tables AS T
INNER JOIN sys.all_columns AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.types TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
ORDER BY 1, 2, 4;
March 19, 2014 at 11:01 am
March 19, 2014 at 11:01 am
I believe that it's easier to use INFORMATION_SCHEMA views.
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CASE WHEN DATA_TYPE IN( 'char', 'varchar', 'nchar', 'nvarchar') THEN '(' + CAST( CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ')'
WHEN DATA_TYPE IN('decimal', 'numeric') THEN '(' + CAST( NUMERIC_PRECISION AS varchar(10)) + ',' + CAST( NUMERIC_SCALE AS varchar(10)) + ')'
ELSE '' END,
CASE WHEN IS_NULLABLE = 'YES' THEN 'NULL' ELSE 'NOT NULL' END,
CASE WHEN DATA_TYPE IN( 'char', 'varchar', 'nchar', 'nvarchar') THEN COLLATION_NAME ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS
You could add more details if needed.
March 19, 2014 at 11:08 am
brn2code (3/19/2014)
Have a look here: http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table
That was my point, but I didn't have an example ready. 😀
I'm making the link clickable to make it easier for others.
March 19, 2014 at 11:17 am
Thanks everyone, really good stuff, I will need to add a few things, will post the results back when I am done
March 19, 2014 at 11:22 am
yep it's possible, but it's not built in.
using the GUI or via SMO is infinitely easier; I know, because i poured weeks of effort down the TSQL-only method, and it's probably only 95% completed.
i made a script i contributed,and an article that gets a little bit of traffic as well; the current version scripts out any table, temp table, or procedure/function/view.
exec sp_GetDDLa TableName;
exec sp_GetDDLa #TempTable;
exec sp_GetDDLa ProcedureName;
see the thread for a lot of different versions from other people who took my version and adapted it to their own needs.
it's like a thousand lines of code, and can still be improved when it comes to scripting the indexes details out.
the thread:
http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx
and the four year old article from 2009:
Get DDL for any SQL 2005 table[/url]
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply