Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to check table creation statement Expand / Collapse
Author
Message
Posted Monday, September 6, 2010 4:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:25 AM
Points: 32, Visits: 130
Hi All

Sorry but silly question .that is their any command or dmv by which we can check create statements of table

like we can we check sp_help 'table name'. it will give table information.

but we need is sp_(some procedure) which gives create definition of table statement. apart from GUI.

or is their any table like sys.comments where we get its information

Post #980999
Posted Monday, September 6, 2010 4:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:36 AM
Points: 2,840, Visits: 3,970
No, unless or until you are tracking it with DDL trigger

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #981001
Posted Monday, September 6, 2010 4:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:25 AM
Points: 32, Visits: 130
can u give me any example please....i am saying that suppose

create table test1 (name varchar(20), age int)
is create so by any mode apart from gui i can check its ddl creation statement if yesreply then i need any dbcc, sp, dmv
if ddl trigger is only method then need the example of it..please
Post #981008
Posted Monday, September 6, 2010 4:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 40,193, Visits: 36,597
If you just want the creation statement, management studio can generated it based on the system tables. From object explorer right click the table, script as -> create

If you want to see the exact statement that was run, you need either a DDL trigger or a trace running at the time the statement was executed.
As for example, why don't you open the documentation and have a look? Quicker than asking here.
http://msdn.microsoft.com/en-us/library/ms189799%28v=SQL.90%29.aspx
http://msdn.microsoft.com/en-us/library/ms186406%28v=SQL.90%29.aspx
http://msdn.microsoft.com/en-us/library/ms175941%28v=SQL.90%29.aspx



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #981020
Posted Monday, September 6, 2010 4:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:36 AM
Points: 2,840, Visits: 3,970
refer these links
http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #981023
Posted Tuesday, September 7, 2010 7:20 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 1, 2014 8:37 AM
Points: 4,783, Visits: 208
If you right click on a table and go to properties it displays the create date of the table.
Post #981553
Posted Tuesday, September 7, 2010 7:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 12,905, Visits: 32,161
i went to the trouble of creating a stored proc that does this;
as previous posters already identified, the actual statement is never saved...all you can do is script out the info as a CREATE TABLE statement, which may be formatted differently than the original statement, but is functionally the same:

my proc is sp_getDDL, and it's usage is pretty simple:

-- USAGE: exec sp_GetDDL YourTableName
-- or exec sp_GetDDL 'bob.example'
-- or exec sp_GetDDL '[schemaname].[tablename]'



so if you create a table like this:
create table dbo.tallycalendar ( thedate datetime not null primary key, dayofweek varchar(50) null, isholiday bit null default(0), isworkholiday bit null default(0), isdaylightsavings bit null default (0), holidayname varchar(100) null, ) 
go
create index ix_tallystuff on tallycalendar (thedate, dayofweek, holidayname)

it produces well formatted results like this:
CREATE TABLE [dbo].[TALLYCALENDAR] ( 
[THEDATE] DATETIME NOT NULL,
[DAYOFWEEK] VARCHAR(50) NULL,
[ISHOLIDAY] BIT NULL
CONSTRAINT [DF__TallyCale__IsHol__023D5A04] DEFAULT ((0)),
[ISWORKHOLIDAY] BIT NULL
CONSTRAINT [DF__TallyCale__IsWor__03317E3D] DEFAULT ((0)),
[ISDAYLIGHTSAVINGS] BIT NULL
CONSTRAINT [DF__TallyCale__IsDay__0425A276] DEFAULT ((0)),
[HOLIDAYNAME] VARCHAR(100) NULL,
CONSTRAINT [PK__TallyCal__5CB7C64E00551192] PRIMARY KEY CLUSTERED (TheDate))

GO

CREATE INDEX [IX_TallyStuff] ON [TallyCalendar] (TheDate, DayOfWeek, HolidayName)


you can download the code here:
Get DDL for any SQL 2005/2008 table

here's the article i contributed discussion with a lot of improvements:
http://www.sqlservercentral.com/Forums/Topic751783-566-1.aspx


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #981594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse