3

I have following situation: I'm working on project where data base contains hundreds of tables, and very often I know what table I need to generate script for, but finding it in the list of all tables takes a lot of time. I would like to know, is there a way to write script which will generate script for table. Since I already know table name, it's easier for me just to type it than search it in huge list of similar names.

3
  • 2
    RedGate has a free SQL search tool that lets you search and will pull up a database object definition when you select the object from the search results red-gate.com/products/sql-development/sql-search Commented Mar 17, 2017 at 16:27
  • 2
    You can use filtering in object explorer if you really need to. Just right click on "Tables" and select Filter -> Filter Settings. FWIW I feel your pain. I have a system that is painful to work with. There are over 4,000 tables and the names started in an old school RPG system so every table has exactly 6 characters in the name and they are all upper case. Commented Mar 17, 2017 at 16:28
  • 1
    See this question/answer on the DBA Stack Exchange site: dba.stackexchange.com/questions/53085/… Commented Mar 17, 2017 at 16:30

2 Answers 2

1

Yes, you can definitely write your own procedure to generate scripts for a given table name which will be provided as parameter. In my example, I am passing 'dbo.TABLE1' as a parameter to this query: (You can wrap this into a stored procedure, of course)

DECLARE  
  @object_name SYSNAME  = 'dbo.TABLE1'
, @object_id INT  = object_id('dbo.TABLE1')
, @SQL NVARCHAR(MAX)  

SELECT  
  @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' +        OBJECT_NAME([object_id]) + ']'  
, @object_id = [object_id]  
FROM (SELECT [object_id] = @object_id) o  



SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) +       STUFF((  
SELECT CHAR(13) + '    , [' + c.name + '] ' +   
    CASE WHEN c.is_computed = 1  
        THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)  
        ELSE   
            CASE WHEN c.system_type_id != c.user_type_id   
                THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name +     ']'   
                ELSE '[' + UPPER(tp.name) + ']'   
            END  +   
            CASE   
                WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')  
                    THEN '(' + CASE WHEN c.max_length = -1   
                                    THEN 'MAX'   
                                    ELSE CAST(c.max_length AS VARCHAR(5))   
                                END + ')'  
                WHEN tp.name IN ('nvarchar', 'nchar')  
                    THEN '(' + CASE WHEN c.max_length = -1   
                                    THEN 'MAX'   
                                    ELSE CAST(c.max_length / 2 AS VARCHAR(5))   
                                END + ')'  
                WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')   
                    THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'  
                WHEN tp.name = 'decimal'  
                    THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'  
                ELSE ''  
            END +  
            CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id   
                THEN ' COLLATE ' + c.collation_name  
                ELSE ''  
            END +  
            CASE WHEN c.is_nullable = 1   
                THEN ' NULL'  
                ELSE ' NOT NULL'  
            END +  
            CASE WHEN c.default_object_id != 0   
                THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +   
                     ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)  
                ELSE ''  
            END +   
            CASE WHEN cc.[object_id] IS NOT NULL   
                THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]  
                ELSE ''  
            END +  
            CASE WHEN c.is_identity = 1   
                THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +   
                                CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'   
                ELSE ''   
            END   
    END  
FROM sys.columns c WITH(NOLOCK)  
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id  
LEFT JOIN sys.check_constraints cc WITH(NOLOCK)   
     ON c.[object_id] = cc.parent_object_id   
    AND cc.parent_column_id = c.column_id  
WHERE c.[object_id] = @object_id  
ORDER BY c.column_id  
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') +   
ISNULL((SELECT '  
, CONSTRAINT [' + i.name + '] PRIMARY KEY ' +   
CASE WHEN i.index_id = 1   
    THEN 'CLUSTERED'   
    ELSE 'NONCLUSTERED'   
END +' (' + (  
SELECT STUFF(CAST((  
    SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +  
            CASE WHEN ic.is_descending_key = 1  
                THEN ' DESC'  
                ELSE ''  
            END  
    FROM sys.index_columns ic WITH(NOLOCK)  
    WHERE i.[object_id] = ic.[object_id]  
        AND i.index_id = ic.index_id  
    FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'  
FROM sys.indexes i WITH(NOLOCK)  
WHERE i.[object_id] = @object_id  
    AND i.is_primary_key = 1), '') + CHAR(13) + ');'  

SELECT   @SQL  
Sign up to request clarification or add additional context in comments.

Comments

0

Here is a link to a stored proc code , you can add and execute by giving just the table name to get the DDL.

http://www.stormrage.com/SQLStuff/sp_GetDDLa_Latest.txt

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.