stuart-d2
6/17/2016 - 6:20 PM

Table Analysis and Create Table ADV Getting all the data_types and columns from an existing table. In preparation for creating a new table w

Table Analysis and Create Table ADV Getting all the data_types and columns from an existing table. In preparation for creating a new table with data types, width, primary key and Null -- only thing it doesnt have is relationships, which i need to figure out laster (schema maybe?) Its best to Create Table with all this info UP FRONT as later simply trying to 'modify' a table to your desire is impossible I have found.

Table Analysis(colHdrs,type and length) and Create Table ADV.sql
content_copyfile_download

--1. The following query gets ALL the header values and ALL the associated datatypes and lengths.
-- After running this, you can copy and paste to the next query to easily create the new table. 
-- just put your tablename within <<yourTableName>>
SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('dbo.<<yourTableName>>')

--
--2. Create table Advanced. Best practice to do all this up front.
-- nullable, data type, primary key
-- only thing it doesnt have is relationships, which i need to figure out laster (schema maybe?)
CREATE TABLE camera_setting
(
camera_setting_id	int NOT NULL PRIMARY KEY,
setting_text	varchar(32) NOT NULL,
setting_short_text	varchar(3) NOT NULL,
type	smallint NOT NULL,
displayable	bit NOT NULL,
editable	bit NOT NULL,
min_limit	int,
max_limit	int
);