Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sankar.c.2006@gmail.com
Starting Member
2 Posts |
 Posted - 2009-04-03 : 03:27:42
|
| Hi I want to see the table scripts using SQL QUERY. IS IT POSSIBLE ?Can anyone help me plz?RegardsSankar |
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
|
|
sankar.c.2006@gmail.com
Starting Member
2 Posts |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-04-03 : 09:51:49
|
| You can use the code inside Madhivanan's procedure but just remove the first few lines and replace with:declare @tableName varchar(100) set @tableName ='xxxx' --Put your tablename hereIf exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName) Or elseyou can generate script for every table in database with:DECLARE @Table_Definition VARCHAR(MAX),@TableName VARCHAR(25)DECLARE TableCursor CURSOR FORselect table_catalog + '.' + table_schema + '.' + table_name as TableName from information_schema.tableswhere table_type='base table'OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @Table_Definition = 'IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ' + QUOTENAME(@TableName,'''') + ')' + CHAR(13) + 'BEGIN'SET @Table_Definition = COALESCE(@Table_Definition + CHAR(13),'') + 'CREATE TABLE(' SELECT @Table_Definition = COALESCE(@Table_Definition,'') +CHAR(13) + Column_Name + SPACE(1) + UPPER(Data_Type) + CASE WHEN data_type IN ('REAL','money','DECIMAL','NUMERIC') THEN '(' + CONVERT(VARCHAR(3),COALESCE(numeric_precision,'')) + ','+ CONVERT(VARCHAR(3),COALESCE(Numeric_Scale,'')) + ')' WHEN data_type IN ('CHAR','nvarchar','VARCHAR','NCHAR') THEN '(' + CAST(isnull(Character_Maximum_Length,'') AS VARCHAR) + ')' ELSE '' END +CASE WHEN Is_Nullable='NO' THEN ' NOT NULL,' ELSE ' NULL,' ENDFROM Information_Schema.ColumnsWHERE Table_Name = @TableNameSET @Table_Definition = STUFF(@Table_Definition,LEN(@Table_Definition),1,CHAR(13) + ')' + CHAR(13) + 'END')PRINT @Table_Definition--EXEC (@Table_Definition)FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor |
 |
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-04-04 : 04:37:25
|
| Hey Thats kk....if i want to script all the store procedure in my database created by user...Thanks... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-04-04 : 07:43:39
|
| Many Many Thanks to u......m just out of my Mind because i get all the values in table but i cant think of that we can also export it through BCP in to .txt file..Many Many Thanks... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-04-04 : 08:27:53
|
quote:
Originally posted by ashishashish
Many Many Thanks to u......m just out of my Mind because i get all the values in table but i cant think of that we can also export it through BCP in to .txt file..Many Many Thanks...
You are welcome  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Table Scripts