Generating a Table Creation Script using SQL Query


DECLARE @TABLE_NAME VARCHAR(100)
DECLARE @CUR_TABLE_NAME CURSOR

DELETE FROM ISGSQLWT01.MONet.dbo.Table_Schema_Info_Prod

SET @CUR_TABLE_NAME = CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where table_name in ('TableName1','TableName2')
OPEN @CUR_TABLE_NAME
FETCH NEXT
FROM @CUR_TABLE_NAME INTO @TABLE_NAME
      WHILE @@FETCH_STATUS = 0
            BEGIN
                  INSERT INTO ISGSQLWT01.MONet.dbo.Table_Schema_Info_Prod
                  SELECT
                        @TABLE_NAME as 'Table Name',
                        c.name as 'Column Name',
                        t.Name as 'Data type',
                        c.[length] as 'Max Length',
                        c.[prec] ,
                        isnull(c.scale,0) ,
                        columnproperty(c.id,c.name,'AllowsNull'),
                        columnproperty(c.id,c.name,'IsIdentity')
                  FROM   
                        syscolumns c , systypes t
                  WHERE
                        c.xtype = t.xtype AND
                        OBJECT_NAME(c.id) = @TABLE_NAME
                       
                FETCH NEXT
                FROM @CUR_TABLE_NAME INTO @TABLE_NAME
            END
CLOSE @CUR_TABLE_NAME
DEALLOCATE @CUR_TABLE_NAME

Comments

Post a Comment

Popular posts from this blog

SSIS vs. T-SQL

This is a common question which comes often before any developer to choose between the two as to follow which path to choose as SSIS or T-SQL for developing ETL solutions. well i also got similar scenario some time back and was in a dilemma to decide this. on a big picture it's always better to go with conventional way i.e. using T-SQL and not to go with the tool. But on doing some more reading and research over many blogs and forums and also the development phase of my work over some 2 and a half odd years. I found some good reasons and explanations over this as not to always stick to a particular approach and do proper analysis of the requirements to take a decision. Below are some of the key points which i was able to collect regarding this. 1.  If the data does need to be persisted temporarily it can be to a raw file in SSIS whereas T-SQL requires temporary tables for which there may be a management/security overhead. 2.  SSIS have a very good visual representatio
Read more

Pump data from one server to another Efficiently

Image
Sometimes the requirement comes when we need to pump the data from one server to another server. Though we have SQL Import\Export wizard which can do this quiet easily, but in that way we will loose some important features of the table --      1. The Identity property of a column in case we have one in the table.      2. All the indexes. To avoid this we can go with a simple SSIS package to which we just need to pass the table name as a parameter and it will do the rest, taking care of the above constraints. This is how the Package looks - Steps - 1. To make the executable dynamic we will drive the package from a table, where we will    store the names of all the tables which we want to refresh from the source server and we will store it in a result set variable (Table_List) Refer the variables list in the below figure used in the package. 2. Create a For each loop container which will iterate throu
Read more

Sync SSAS Cube From One Environment to Another

Please Execute the Below Mentioned Script in Either the SQL Agent Job or XMLA Script Window or in the SSIS Package using the "Analysis Services Execute DDL Task" -  < Synchronize  xmlns:xsi = " https://www.w3.org/2001/XMLSchema-instance " xmlns:xsd = " https://www.w3.org/2001/XMLSchema " xmlns = " https://schemas.microsoft.com/analysisservices/2003/engine " > < Source > < ConnectionString > Provider=MSOLAP.6;Data Source=SOURCESERVER; Integrated Security=SSPI;Initial Catalog=SourceCubeName </ ConnectionString > < Object > < DatabaseID > SourceCubeName </ DatabaseID > </ Object > </ Source > < SynchronizeSecurity > CopyAll </ SynchronizeSecurity > < ApplyCompression > true </ ApplyCompression > </ Synchronize >
Read more