Posts

Showing posts from August, 2013

Scripting Indexes in SQL 2000

DECLARE IndexCursor CURSOR FOR SELECT OBJECT_NAME ( SI . ID ), SI . ID , SI . Name , SI . indid FROM dbo . sysindexes SI LEFT JOIN INFORMATION_SCHEMA . TABLE_CONSTRAINTS TC ON SI . Name = TC . CONSTRAINT_NAME AND OBJECT_NAME ( SI . ID ) = TC . TABLE_NAME WHERE TC . CONSTRAINT_NAME IS NULL AND OBJECTPROPERTY ( SI . ID , 'IsUserTable' ) = 1 AND INDEXPROPERTY ( SI . [id] , SI . [name] , 'IsStatistics' ) = 0 AND OBJECT_NAME ( SI . ID ) in ( 'Table1' , 'Table2' ) ORDER BY OBJECT_NAME ( SI . ID ), SI . indid DECLARE @IxTable varchar ( 50 ) DECLARE @IxTableID INT DECLARE @IxName varchar ( 50 ) DECLARE @IxID INT -- Loop through all indexes OPEN IndexCursor FETCH NEXT FROM IndexCursor INTO @IxTable , @IxTableID , @IxName , @IxID WHILE ( @@FETCH_STATUS = 0 ) BEGIN    DECLARE @IXSQL NVARCHAR ( 4000 )    SET @IXSQL = 'CREATE '    -- Check if the

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' ),